COUNTIF & SUBTOTAL

[Dickie Worton]

Hi!

Not sure if anyone can help with this one at all...

I have a large spreadsheet into which I have added a column containing a
COUNTIF function to count how many iterations of a value appear in that
column. Here it is:

=COUNTIF($B$9:$B$1271,B146)

However, I also have filters applied on this worksheet to help me identify
records that meet a particular criteria. The problem I have is that if I
filter on values in column C, D, etc, the function above doesn't adjust to
take account of the fact that some of the records (rows) are now hidden, e.g.
if a value appears 10 times in column B, but only four times with the filter
applied, the function will still return a value of '10'.

Is there any way to make the COUNTIF function have the characteristics of a
SUBTOTAL function, i.e. to ignore hidden rows when returning a value? I think
some other postings on here may have asked the same thing (in a roundabout
way) & the suggested response was to use the data in a pivot table instead.
However, I was hoping not to go down that route (yet)...

Any help gratefully received.....

Thanks,

Dickie

 

[Bob Phillips]

You don't need to. SUBTOTAL has a count option, 2 for numeric values, 3 for
any value

=SUBTOTAL(3, rng)

If you wnat SUBTOTAL with a condition, try

=SUMPRODUCT(SUBTOTAL(3,OFFSET($B$9,ROW($B$9:$B$1271)-ROW($B$9),,1)),--($B$9:$B$1271=B146))

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Lori]

To only count the filtered rows, maybe try filling down instead:

=COUNTIF($B$9:$B$1271,B146)*SUBTOTAL(3,B146)