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



"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?

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?

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?

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.

On Nov 3, 4:20*pm, 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?

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

If you can use AutoFilter to exclude the rows that do not contain the
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 that
contain the text value.


--
shg
------------------------------------------------------------------------
shg's Profile: http://www.thecodecage.com/forumz/member.php?userid=13
View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=24671

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

"Peo Sjoblom" wrote:

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?

Thanks for the feedback

--


Regards,


Peo Sjoblom

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

"Peo Sjoblom" wrote:

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?