COUNTIF on filtered items only

[Guest]

I'm using the subtotal function to total the amount of rows i have showing
after a column has been filtered. I want to set up COUNTIF functions that
will count a specific word within the filtered rows. If i select a range
using COUNTIF, it counts even thoughs rows hidden by filter.

To give an idea, I use filter to filter data by date. The subtotal shows
how many rows of entries i have in that date. From there, i want to have
COUNTIFs set up to automatically show how often a text string shows. For ex.
out of a year of 400 entries, June has 60 entries. 35 are "no" leading to a
58% error ratio.

COUNTIF will not count the 35 "no" of the 60, it will calculate the "no"s of
the 400, and the error ratio calculator will divide the year of "no"s by 60.

 

[Don Guillett]

have a look in HELP index for SUBTOTAL

--
Don Guillett
SalesAid Software
(e-mail address removed)

I'm using the subtotal function to total the amount of rows i have showing
after a column has been filtered. I want to set up COUNTIF functions that
will count a specific word within the filtered rows. If i select a range
using COUNTIF, it counts even thoughs rows hidden by filter.

To give an idea, I use filter to filter data by date. The subtotal shows
how many rows of entries i have in that date. From there, i want to have
COUNTIFs set up to automatically show how often a text string shows. For ex.
out of a year of 400 entries, June has 60 entries. 35 are "no" leading to a
58% error ratio.

COUNTIF will not count the 35 "no" of the 60, it will calculate the "no"s of
the 400, and the error ratio calculator will divide the year of "no"s by

60.

 

[Frank Kabel]

Hi
if column B conatins the 'no's try:
=SUMPRODUCT(--(SUBTOTAL(3,OFFSET(INDEX($A$1:$A$1000,1,1),ROW($A$1:$A$10
00)-ROW(INDEX($A$1:$A$1000,1,1)),0))=1),--($B$1:$B$1000="no"))