Subtotal - Based on DATE

[Danny]

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]

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<>""))

 

[Don Guillett]

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)*b2:b22)

 

[Danny]

Thanks a lot Dave!

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]

Thanks for the tip Don. I'll try it sometime.

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)*b2:b22)

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(e-mail address removed)