Summing based on a range of dates

[Dale]

In column A I have dates for the entire year in one week increments, I.e
1/1/2009, 1/8/2009, etc.. In column B I have the number of customer acquired
during that week. I want to now sum the number of customers by each month.
This will allow me to develop acquisition nnumbers against marketing pieces
mailed each week instead of totaling them for the month. I am looking for a
sum feature that will read and sum values within the range of 1/1/2009 -
1/29/2009, 2/5/2009 - 2/26/2009, etc..

Any suggestions?

Thanks

Dale

 

[Peo Sjoblom]

=SUMPRODUCT(--(YEAR(A2:A1000)=2009),--(MONTH(A2:A1000)=1),B2:B1000)

The above would be for Jan 2009, do the same for all the other months


--


Regards,


Peo Sjoblom

 

[JC]

If you want to sum specific dates ranges (rather than by months) you will
need to specify in the formula itself. Try the following:
=SUMIF(A2:A100,">=01/01/2009",B2:B100)-SUMIF(A2:A100,">01/29/2009",B2:B100)

change the ranges to match your specific spreadsheet. You will need to
repeat for each date range required.

 

[Dale]

I found a work around by taking adding a column with the following formula
=text(A3,"mmmm"). This converted the date ranges to the proper month and the
completed my sumif function based on the month name.

Dale