Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Summing based on a range of dates
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Summing based on a range of dates
=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 "Dale" wrote in message ... 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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Summing based on a range of dates
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" wrote: 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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Summing based on a range of dates
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 "Dale" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How does one get excel sum of a column based on given dates range | Excel Worksheet Functions | |||
I want to use sumif to sum up a column based on a range of dates | Excel Worksheet Functions | |||
Summing a range based on value criteria | Excel Discussion (Misc queries) | |||
Lookup based on range of dates | Excel Worksheet Functions | |||
return based on range of dates | Excel Worksheet Functions |