Subtotal Formula with a Catch (Excel 2002)

Topic

New Reply

Hi!!

I’m looking for a formula, as opposed to using a pivot table (a pivot table won’t work for all the things I wish to do

Reply | Quote

Replies

Enter Joe in cell E2, and this array formula (confirm with Ctrl+Shift+Enter) in F2:

=SUM(IF(($A$2:$A$31<YEAR(TODAY()))*($B$2:$B$31=E2),$C$2:$C$31))

Enter the other names in E3 and E4, then fill down the formula from F2 to F4.

Reply | Quote

Thanks Hans… of course that works great… I follow most of the formula but the * throws me off… to me that means multiply… can you expain the formula in a few words please so I understand the logic of it a bit more.
Thanks again Hans!
Lana

Reply | Quote

The formula uses TRUE = 1 and FALSE = 0.

The part $A$2:$A$31<YEAR(TODAY()) results in a series of TRUE/FALSE values, and the part $B$2:$B$31=E2 too. Multiplying them results in 1 if both are 1 (TRUE), otherwise in 0. So multiplying has the same effect as AND.

Reply | Quote