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

  • Thread starter Thread starter RABrown
  • Start date Start date
R

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?
 
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
 
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?
 
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
 
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.
 
Thank you so much, Peo. I don't understand WHY or HOW your formula
suggestion works, but it DOES. THANKS!
 
Back
Top