Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
sumproduct with dates
I have a quarterly time series and I always want to sum the last three
quartes of a year and the first of the next year and I can't think of a simple way to do it. e.g. A B 31/3/2005 1000 30/6/2005 3500 30/9/2005 6790 31/12/2005 8000 31/3/2006 9000 So I would want to add quarters 6, 9 and 12 of 2005 and 3 of 2006 in this case but in general quarters 6, 9, 12 of year xxxx and 3 of year xxxx+1 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
sumproduct with dates
with year in C1 i.e. 2005: =SUMPRODUCT(--(MONTH(A1:A5)3),--(YEAR(A1:A5)=C1),(B1:B5))+SUMPRODUCT(--(MONTH(A1:A5)=3),--(YEAR(A1:A5)=C1+1),(B1:B5)) HTH "Bumblebee" wrote: I have a quarterly time series and I always want to sum the last three quartes of a year and the first of the next year and I can't think of a simple way to do it. e.g. A B 31/3/2005 1000 30/6/2005 3500 30/9/2005 6790 31/12/2005 8000 31/3/2006 9000 So I would want to add quarters 6, 9 and 12 of 2005 and 3 of 2006 in this case but in general quarters 6, 9, 12 of year xxxx and 3 of year xxxx+1 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
sumproduct with dates
thank you, it worked
"Toppers" wrote: with year in C1 i.e. 2005: =SUMPRODUCT(--(MONTH(A1:A5)3),--(YEAR(A1:A5)=C1),(B1:B5))+SUMPRODUCT(--(MONTH(A1:A5)=3),--(YEAR(A1:A5)=C1+1),(B1:B5)) HTH "Bumblebee" wrote: I have a quarterly time series and I always want to sum the last three quartes of a year and the first of the next year and I can't think of a simple way to do it. e.g. A B 31/3/2005 1000 30/6/2005 3500 30/9/2005 6790 31/12/2005 8000 31/3/2006 9000 So I would want to add quarters 6, 9 and 12 of 2005 and 3 of 2006 in this case but in general quarters 6, 9, 12 of year xxxx and 3 of year xxxx+1 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Pre-1900 dates | Excel Discussion (Misc queries) | |||
Need to pull current dates from list w/many dates | Excel Discussion (Misc queries) | |||
Default Dates | Excel Discussion (Misc queries) | |||
sumproduct between two dates | Excel Worksheet Functions | |||
Formating Dates for production schedule | Excel Discussion (Misc queries) |