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.

 

[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