Criteria-Based Subtotals

[Michael]

Hi! I was wondering what the best way would be to subtotal
a column if it met certain criteria. For instance,
{=IF(AND(a2:a100="NFC",b2:b100<today()),sum(k2:k100))}

The result of using the array is FALSE because the last
condition IS false. That tells me it's not adding each
cell that meets the condtions to a running subtotal. This
is easy to do in SAS with a SET BY, but I can't get Excel
to replicate that action.

Any suggestions?

Thank you!!

Michael

 

[Frank Kabel]

Hi
try
=SUMPRODUCT((A2:A100="NFC")*(B2:B100<TODAY()),K2:K100)

 

[Michael]

Frank, you are a saint. That is the coolest thing I've
ever seen. I've been using Excel extensively for years and
would never have come up with that. THANKS SO MUCH!!

Leave work early and go get a warm beer on me!

Michael

 

[Tom Ogilvy]

Just move your sum and replace AND with multiplication

=Sum(IF((a2:a100="NFC")*(b2:b100<today()),(k2:k100))

and array enter it.

 

[Frank Kabel]

Frank, you are a saint. That is the coolest thing I've
ever seen. I've been using Excel extensively for years and
would never have come up with that. THANKS SO MUCH!!

Leave work early and go get a warm beer on me!

Michael
I prefer a COLD beer )
thanks for your feedback
Frank