Subtotal Function

[Aaron]

Hi,

I am using a "Subtotal" Function in a worksheet that I
have to sum certain columns that I filter on. However, I
need to combine a "Subtotal" function with a "Countif"
function. I need a function that when I filter on a
column with Yes or No inputted, I know how many Yes cells
there are within that filtered range. Any help would be
much appreciated.

Thanks,
Aaron

 

[Frank Kabel]

Hi
if you filter with column A and your Yes/No values are in column B try:
=SUMPRODUCT(--(SUBTOTAL(3,OFFSET(INDEX(Tabelle1!$A$1:$A$100,1,1),ROW(Ta
belle1!$A$1:$A$100)-ROW(INDEX(Tabelle1!$A$1:$A$100,1,1)),0))=1),--(Tabe
lle1!$B$1:$B$100="Yes"))

 

[Debra Dalgleish]

The SUBTOTAL function can use different numbers as its first argument.
For example: =SUBTOTAL(9,A1:A100)
will SUM the values in the range

To count, you can use: =SUBTOTAL(3,A1:A100)
to count the cells that are not empty

Or: =SUBTOTAL(2,A1:A100)
to count the numbers in the range