CountIF in a subtotal?

  • Thread starter Thread starter Axeman
  • Start date Start date
A

Axeman

Hi boys & girls

I'm aware that you can't do COUNTA and COUNT if you're using a
autofiltered list (well you can but it still counts all the rows, no
just the filtered ones), but you can replicate their function usin
SUBTOTAL(....,..) to just get summaries according to the displaye
rows.

However, there doesn't appear to be a SUBTOTAL equivalent of th
COUNTIF function. Does anyone know a way to get around this (th
project isn't worth me actually coding a solution, so workshee
functions only, please.)

ie I want to be able to see count all the rows that match a certai
value, but only if they're visible when the filter is on.

Thanks in advanc
 
There are workarounds, it would be easier if you posted an example what the
countif criteria
would be, however assume you have values in A2:A100 and you want to countif
the filtered values
where you already ran out of filtering options and you need one more
criteria,assume you want to count the filtered values greater than 5

=SUMPRODUCT(($A$2:$A$100>5)*(SUBTOTAL(3,OFFSET($A$2,ROW($A$2:$A$100)-MIN(ROW
($A$2:$A$100)),,))))
 
Many thanks Peo, that did the trick perfectly

(Now just need to work out what you did, so I can believe it works!)


Ta again

Mark
 
I believe the great Laurent Longre is the originator and what it does it
creates
and array of the visible values and turn them into 1s

SUBTOTAL(3,OFFSET($A$2,ROW($A$2:$A$100)-MIN(ROW($A$2:$A$100)),,))

that part will return 1 for visible cells and 0 for hidden cells

$A$2:$A$100>5

will return TRUE or FALSE and then when multiplied it will return 1 for each
hit where the second array is 1 and the first is TRUE and then sumproduct
will sum those 1s and return the count, it can also be rewritten as

=SUMPRODUCT(--($A$2:$A$100>5),--(SUBTOTAL(3,OFFSET($A$2,ROW($A$2:$A$100)-MIN
(ROW($A$2:$A$100)),,))))

and if you want to sum the conditions like a sumif


=SUMPRODUCT(--($A$2:$A$100>5),--(SUBTOTAL(3,OFFSET($A$2,ROW($A$2:$A$100)-MIN
(ROW($A$2:$A$100)),,))),$A$2:$A$100)
 
Back
Top