# Need Excel Formula

Discussion in 'Business Applications' started by wilj0069, Aug 29, 2007.

Not open for further replies.
1. I need a formula that will lookup between 2 given date ranges and return a sum of numbers in a specific column. For example, I want to sum all dividends for symbol EEF in between the dates of 12/29/2006 and 3/13/2007. I have a sheet set up with all symbols, dates, and dividends that are needed, but i can't figure out a lookup function to only return the values over a given range of time. I hope this makes sense. Thank You

Brandon

2. ### Zack Barresse

Joined:
Jul 25, 2004
Messages:
5,452
Hi there, welcome to the board!

You could use this ...

=SUMPRODUCT((A2:A20=F4)*(B2:B20>=F2)*(B2:B20<=F3),C2:C20)

This assumes that your symbols are in A2:A20, dates are in B2:B20, and dividends are in C2:C20. Furthermore, the criteria is assumes to be start date in F2, end date in F3, and symbol criterion in F4.

HTH

3. I am sort of understanding this, however it doesn't quite work with the set up I have. I have the symbols from B-Z across the top. I have the dates from A1-A100 lets say down the side and the dividends for each date and symbol in the matching box accordingly. Is there anyway you can help with that? Thank you in advance

4. ### Zack Barresse

Joined:
Jul 25, 2004
Messages:
5,452
The ranges need to be of the same size here for this to work. Is there any way you can post a sample of your file for us to work with?

5. This is one of about 100 symbols I have on this sheet. Mabe it will help.

File size:
16 KB
Views:
283
6. ### slurpee55

Joined:
Oct 20, 2004
Messages:
7,837
Not much information - it looks as if you just are needing a simple SUM of the data in column B (e.g., to get the sum of the data from 12/29/2006 to 3/13/2007 you would put somewhere - say C1 - the formula =SUM(B2:B50) ). But I suspect that is not all you want to do?

7. No I have 100 of those and on a separate project I want to be able to put in a date range (12/29/2006 and 3/13/2007) and a symbol (IEF) and have excel sum all the dividends in that given time period. I don't know if it is a lookup function or what. I used a matching formula to find a single cell to match the date and symbol to a price, but am looking for someway to use that and sum all the dividends in that date range. Here is a larger sample to help you better understand.

File size:
18.5 KB
Views:
133
8. ### Zack Barresse

Joined:
Jul 25, 2004
Messages:
5,452
You can use this formula...

=SUMIF(\$A\$2:\$A\$84,">="&\$Q\$1,OFFSET(\$A\$2,0,MATCH(\$Q\$3,\$B\$1:\$N\$1,0)):OFFSET(\$A\$84,0,MATCH(\$Q\$3,\$B\$1:\$N\$1,0)))-SUMIF(\$A\$2:\$A\$84,">"&\$Q\$2,OFFSET(\$A\$2,0,MATCH(\$Q\$3,\$B\$1:\$N\$1,0)):OFFSET(\$A\$84,0,MATCH(\$Q\$3,\$B\$1:\$N\$1,0)))

You'll need some values in the cells above, start date in Q1, end date in Q2, symbol in Q3. I'll upload your sample revised with the formula.

HTH

#### Attached Files:

• ###### sample(1).xls
File size:
23 KB
Views:
264
9. Thank you very much. This is exactly what I needed.

10. ### Zack Barresse

Joined:
Jul 25, 2004
Messages:
5,452
Sure, no problem.

One thing I forgot to ask, would this list of your be growing in either direction? If so, you may want to think about making this dynamic.