subtotal countif

[fletch]

I've been using the subtotal function with autofilters to only count the entries which are visible (not filtered out with the auto filter).

I would like to get a subtotal for the function:

=COUNTIF(N8:N10853,"MISSING")

but there does not appear to be a function_number for a countif statement.

Is there another way?

 

[Zack Barresse]

How about something like ...

(untested)

=SUMPRODUCT(SUBTOTAL(3,OFFSET(N8:N10853,ROW(N8:N10853)-ROW(N8),0,1)),--(N8:N10853="MISSING"))

 

[fletch]

perfect - thanks

 

[Zack Barresse]

You're welcome.

Also, when your data is filtered, if you select it then your StatusBar info (bottom right, right-click to set mathematical operation) will reflect the filtered data as well.

 

[syang]

How about something like ...

(untested)

=SUMPRODUCT(SUBTOTAL(3,OFFSET(N8:N10853,ROW(N8:N10853)-ROW(N8),0,1)),--(N8:N10853="MISSING"))

Thank you for the formula. I was wondering if you could explain why the third cell reference has to be just N8 and not N8:N10853, like the other cell references. I tried it both ways and they both work, but the formula where I make all the cell references N8:N10853 does not always display accurate numbers when I filter my list. I just wanted to know so I can understand Excel better. This is the formula I tried:

=SUMPRODUCT(SUBTOTAL(3,OFFSET(N8:N10853,ROW(N8:N10853)-ROW(N8:N10853),0,1)),--(N8:N10853="MISSING"))


Thanks!