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

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$127 1)-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)



"Dickie Worton" wrote in message
...
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

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

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

On Oct 31, 10:07 am, Dickie Worton
wrote:
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