Using COUNTIF function on a filtered result

  • Thread starter Thread starter Mike U.
  • Start date Start date
M

Mike U.

Does anyone know how to configure the COUNTIF function to
only count the number of matching variables in a column in
a filtered result? I need the flexibility to filter my
data in various ways and I need the corresponding "counts"
to relate only to the filtered data. Currently, I continue
to get the same "counts" for the whole worksheet with or
without filters applied.

Any suggestions would be appreciated.

Thanks
 
Hi
you may have a look at the SUBTOTAL function to count the number of
entries based on a filerted result. See the Excel helpfile for more
details on this function
 
One way

=SUMPRODUCT(--($B$2:$B$200>2),SUBTOTAL(3,OFFSET($B$2,ROW($B$2:$B$200)-MIN(RO
W($B$2:$B$200)),,)))

where a countif if it worked would look like

=COUNTIF($B$2:$B$200,">2")

for text values

=SUMPRODUCT(--($B$2:$B$200="X"),SUBTOTAL(3,OFFSET($B$2,ROW($B$2:$B$200)-MIN(
ROW($B$2:$B$200)),,)))

equivalent to

=COUNTIF($B$2:$B$200,"X")
 
Check the "Subtotal" function -- it will count the number
of items showing when a filter is applied:

=SUBTOTAL(2,A:A)

I use the above to count the number of records in column
A -- the "2" indicates the count function. If anyone
knows how to use this and only capture unique records --
let me know!
 
One way

=SUM(--IF(SUBTOTAL(3,OFFSET(A2,ROW(A2:A100)-MIN(ROW(A2:A100)),,1)),(MMULT(((
A2:A100=TRANSPOSE(A2:A100)*TRANSPOSE(SUBTOTAL(3,OFFSET(A2,ROW(A2:A100)-MIN(R
OW(A2:A100)),,1))))*(ROW(A2:A100)>=TRANSPOSE(ROW(A2:A100)))),ROW(A2:A100)*0+
1)=1)))

entered with ctrl + shift & enter
 
Wow! That's incredible! Of course, it's going to take me
the rest of the day to dissect it to figure out how it
works... Works perfectly -- thanks!
 
Back
Top