COUNTIF on filtered items only

  • Thread starter Thread starter Guest
  • Start date Start date
G

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.
 
have a look in HELP index for SUBTOTAL

--
Don Guillett
SalesAid Software
(e-mail address removed)
Underdog said:
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
Click to expand...
60.
 
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"))
 
Back
Top