Is it possible to ONLY count values that appear within UNHIDDEN ce

[RABrown]

I have several rows of data that are HIDDEN and I don't want to count the
data within them. When I use COUNTIF, it counts the values even in the
hidden rows. Is it possible to restrict the COUNTIF function only to
UNHIDDEN cells?

 

[Duke Carey]

depending on your Excel version, the Subtotal function has arguments that
allow you to SUM/COUNT/AVERAGE, etc on only displayed rows

So

=subtotal(9,a1:a100) will SUM the entire range

=subtotal(109,a1:a100) will SUM only displayed cells in the range

 

[RABrown]

Thank you for your help, Duke ....but I need a little more. I want to count
the number of UNHIDDEN cells with a given TEXT value. As I understand the
"Subtotal" function, I may be able to use the Subtotal function #103 (COUNTA)
to do this, but I don't know how to include within the Subtotal function the
exact text which I'm looking for. Can you help?

 

[Peo Sjoblom]

Not good from a design point of view though


=SUMPRODUCT(--($A$2:$A$500="text"),(SUBTOTAL(103,OFFSET($A$2,ROW($A$2:$A$500)-MIN(ROW($A$2:$A$500)),,))))


adapt it to fit your own requirements, if basically works as if you could
use


=COUNTIF($A$2:$A$500,"text")


except that it only counts visible cells

also note that it won't work in version
earlier than 2003, if you have that you would need
VBA to create a UDF

--


Regards,


Peo Sjoblom

 

[Reitanos]

As Duke mentioned, SUBTOTAL is great. It works with either filtered or
hidden data.

Excel Help is recommended on this one because it does get complicated:
1 is average, 2 is count, 3 is counta, etc. I use this any time I have
an autofilter on a sheet.

 

[shg]

SUBTOTAL does not include COUNTIF, as you've found.

If you can use AutoFilter to exclude the rows that do not contain th
particular text value in the column of interest, you can use SUBTOTAL(3
...) or SUBTOTAL(103, ...) (COUNTA).

However, that will unhide any rows that you have manually hidden tha
contain the text value

 

[RABrown]

Thank you so much, Peo. I don't understand WHY or HOW your formula
suggestion works, but it DOES. THANKS!

 

[Peo Sjoblom]

Thanks for the feedback

--


Regards,


Peo Sjoblom