# Thread: Subtotal Formula with a Catch (Excel 2002)

1. ## Subtotal Formula with a Catch (Excel 2002)

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

2. ## Re: Subtotal Formula with a Catch (Excel 2002)

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.

3. ## Re: Subtotal Formula with a Catch (Excel 2002)

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

4. ## Re: Subtotal Formula with a Catch (Excel 2002)

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.

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•