Calculate Filtered Values (with Criteria)

[wasteland]

Hello-

I have found a lot of answers on boards like these, but not a lot of time to give back. Hopefully this will benefit someone...

Seems that the concensus for calculating filtered rows AND to be able to apply criteria is to use SumProduct like this:

SUMPRODUCT(--(C3:C9=C14),--(G3:G9=B15),SUBTOTAL(9, OFFSET(H3:H9,ROW(H3:H9)-ROW(H3),0,1)))
​

I tried that and tried UDFs (like IsHidden) and everything seemed convoluted and/or slow.


This has worked well for me...


In the table or range of data, enter a new column named "Filtered" and add the following formula:

= - - SUBTOTAL(2,A2)
​

This will give a count of the row in which the formula resides. If the row is hidden (filtered), it will return 0. If not hidden, it will return 1.

The double unary ( - -) is used to change the TRUE/FALSE to 1/0 values.
It is also necessary because at the end of a table or range, Excel apparently determines that certain Subtotal rows should not be counted.

So now that there is a Filtered flag, it can be used in any criteria.

Can still use SumProduct- just don't need the SubTotal and Offset part.

In Excel 2007, with structured naming and SumIFS, formulas are a lot cleaner:
=SUMIFS(Sales[Cost],Sales[Filtered],1,Sales[Month Sold],CurrentMonth)