| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Subtotal - Based on DATE
Hi,
My formula below retunrs "0" because this year, I think "12" refers to December 2008. The formula below was to produce December 2007. Please edit the formula below so it can produce December 2007 and/or December of ANY year that I will select in the future. =SUMPRODUCT(--(TEXT('Processing'!$J$2:$J1995,"m")="12"),--('Processing'!$J$2:$J1995<"")) Thank you. |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Subtotal - Based on DATE
12 refers to December in any year.
If you want December of 2007: =SUMPRODUCT(--(TEXT('Processing'!$J$2:$J1995,"yyyymm")="200712") , --('Processing'!$J$2:$J1995<"")) Danny wrote: Hi, My formula below retunrs "0" because this year, I think "12" refers to December 2008. The formula below was to produce December 2007. Please edit the formula below so it can produce December 2007 and/or December of ANY year that I will select in the future. =SUMPRODUCT(--(TEXT('Processing'!$J$2:$J1995,"m")="12"),--('Processing'!$J$2:$J1995<"")) Thank you. -- Dave Peterson |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Subtotal - Based on DATE
Thanks a lot Dave!
"Dave Peterson" wrote: 12 refers to December in any year. If you want December of 2007: =SUMPRODUCT(--(TEXT('Processing'!$J$2:$J1995,"yyyymm")="200712") , --('Processing'!$J$2:$J1995<"")) Danny wrote: Hi, My formula below retunrs "0" because this year, I think "12" refers to December 2008. The formula below was to produce December 2007. Please edit the formula below so it can produce December 2007 and/or December of ANY year that I will select in the future. =SUMPRODUCT(--(TEXT('Processing'!$J$2:$J1995,"m")="12"),--('Processing'!$J$2:$J1995<"")) Thank you. -- Dave Peterson |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Subtotal - Based on DATE
try this idea where a1 has 2007 and b1 has 12 or replace =b1 with =12.
Assumes DATES in col A and values to sum in col B =sumproduct((year(a2:a22)=a1)*(month(a2:a22)=b1)*b 2:b22) -- Don Guillett Microsoft MVP Excel SalesAid Software "Danny" wrote in message ... Hi, My formula below retunrs "0" because this year, I think "12" refers to December 2008. The formula below was to produce December 2007. Please edit the formula below so it can produce December 2007 and/or December of ANY year that I will select in the future. =SUMPRODUCT(--(TEXT('Processing'!$J$2:$J1995,"m")="12"),--('Processing'!$J$2:$J1995<"")) Thank you. |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Subtotal - Based on DATE
Thanks for the tip Don. I'll try it sometime.
"Don Guillett" wrote: try this idea where a1 has 2007 and b1 has 12 or replace =b1 with =12. Assumes DATES in col A and values to sum in col B =sumproduct((year(a2:a22)=a1)*(month(a2:a22)=b1)*b 2:b22) -- Don Guillett Microsoft MVP Excel SalesAid Software "Danny" wrote in message ... Hi, My formula below retunrs "0" because this year, I think "12" refers to December 2008. The formula below was to produce December 2007. Please edit the formula below so it can produce December 2007 and/or December of ANY year that I will select in the future. =SUMPRODUCT(--(TEXT('Processing'!$J$2:$J1995,"m")="12"),--('Processing'!$J$2:$J1995<"")) Thank you. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Unhide column based on subtotal level open. | Excel Discussion (Misc queries) | |||
| Subtotal based discounts | Excel Discussion (Misc queries) | |||
| How to calculate shipping costs based on subtotal | Excel Worksheet Functions | |||
| subtotal based on two conditions | Excel Discussion (Misc queries) | |||
| I need to subtotal based on an if | Excel Worksheet Functions | |||
Hybrid Mode
