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

"RABrown" wrote in message
...
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?

"Duke Carey" wrote:

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" wrote:

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?