Ability to sum data by varying dates

C

chopsx7

I have numerous lists of data going back several years organized simply col1
- date col2-data. Frequently I need to go back and provide sum totals
(sometimes avg etc) over a requested date range. Is there a way to utilize a
lookup function of some sort whereby I can enter a start and end date and
have excel sum the range based on the dates? Now I manually check the lists
to figure out the range that needs to be included in the calculation.
Thanks
 
R

Ron Rosenfeld

I have numerous lists of data going back several years organized simply col1
- date col2-data. Frequently I need to go back and provide sum totals
(sometimes avg etc) over a requested date range. Is there a way to utilize a
lookup function of some sort whereby I can enter a start and end date and
have excel sum the range based on the dates? Now I manually check the lists
to figure out the range that needs to be included in the calculation.
Thanks
Click to expand...

Look at the SUMIF worksheet function:

e.g.:

=SUMIF(col1, ">=" & start_date, col2) -
SUMIF(col1, ">" & end_date, col2)


--ron
 
B

Bob Phillips

=SUMPRODUCT(--(A2:A10000>=--"1999-01-01"),--(A2:A10000<=--"2002-12-31"),B2:B
10000)

or put the dates in some space cells and use those

=SUMPRODUCT(--(A2:A10000>=M1),--(A2:A10000<=M2),B2:B10000)
 
Top