=counta() unless row is hidden
 

hi all
in column A i have some text data. some of the rows have been
hidden. i want to count how many are left (visible). but any COUNT
functions count the hidden rows, too. any ideas?
thanks a lot in advance.
susan

=counta() unless row is hidden
 

Check out the helo for the below function...The below will return the number
of cells with data in Column B which are visible...Please note that hidden
cells are hidden through menu...
=SUBTOTAL(103,B:B)

If this post helps click Yes
---------------
Jacob Skaria


"Susan" wrote:

hi all
in column A i have some text data. some of the rows have been
hidden. i want to count how many are left (visible). but any COUNT
functions count the hidden rows, too. any ideas?
thanks a lot in advance.
susan

=counta() unless row is hidden
 

thanks, jacob, that works great at work on my 2007 excel here at
work. i'll keep it in mind for a good function to use! got any ideas
for <2007 versions? when i open the old 2000 version it doesn't work,
& at home i only have '03.
thanks again
susan



On Aug 21, 2:43*pm, Jacob Skaria
wrote:
Check out the helo for the below function...The below will return the number
of cells with data in Column B which are visible...Please note that hidden
cells are hidden through menu...
=SUBTOTAL(103,B:B)

If this post helps click Yes
---------------
Jacob Skaria



"Susan" wrote:
hi all
in column A i have some text data. *some of the rows have been
hidden. *i want to count how many are left (visible). *but any COUNT
functions count the hidden rows, too. *any ideas?
thanks a lot in advance.
susan- Hide quoted text -

- Show quoted text -

=counta() unless row is hidden
 

Works for 2003 and greater versions
--
If this post helps click Yes
---------------
Jacob Skaria


"Susan" wrote:

thanks, jacob, that works great at work on my 2007 excel here at
work. i'll keep it in mind for a good function to use! got any ideas
for <2007 versions? when i open the old 2000 version it doesn't work,
& at home i only have '03.
thanks again
susan



On Aug 21, 2:43 pm, Jacob Skaria
wrote:
Check out the helo for the below function...The below will return the number
of cells with data in Column B which are visible...Please note that hidden
cells are hidden through menu...
=SUBTOTAL(103,B:B)

If this post helps click Yes
---------------
Jacob Skaria



"Susan" wrote:
hi all
in column A i have some text data. some of the rows have been
hidden. i want to count how many are left (visible). but any COUNT
functions count the hidden rows, too. any ideas?
thanks a lot in advance.
susan- Hide quoted text -

- Show quoted text -

=counta() unless row is hidden
 

ok, thanks!
susan


On Aug 21, 3:38*pm, Jacob Skaria
wrote:
Works for 2003 and greater versions
--
If this post helps click Yes
---------------
Jacob Skaria



"Susan" wrote:
thanks, jacob, that works great at work on my 2007 excel here at
work. *i'll keep it in mind for a good function to use! *got any ideas
for <2007 versions? *when i open the old 2000 version it doesn't work,
& at home i only have '03.
thanks again
susan

On Aug 21, 2:43 pm, Jacob Skaria
wrote:
Check out the helo for the below function...The below will return the number
of cells with data in Column B which are visible...Please note that hidden
cells are hidden through menu...
=SUBTOTAL(103,B:B)

If this post helps click Yes
---------------
Jacob Skaria

"Susan" wrote:
hi all
in column A i have some text data. *some of the rows have been
hidden. *i want to count how many are left (visible). *but any COUNT
functions count the hidden rows, too. *any ideas?
thanks a lot in advance.
susan- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -

=counta() unless row is hidden
 

Jacob,
Works also if the rows have been hidden via macro, as
Range("A2:A6").Rows.EntireRow.Hidden=True
will exempt values in rows 2:6 from the subtotal.

I just had to check that out (Excel 2003) because I have use for it, and I
always forget about the 103 type function feature of SubTotal() which your
post reminded me about. Thanks for the reminder.

Also works for auto-filtered data, but you'd probably want to subtract 1
from that result to account for the auto-filter label row.
Jerry

"Jacob Skaria" wrote:

Check out the helo for the below function...The below will return the number
of cells with data in Column B which are visible...Please note that hidden
cells are hidden through menu...
=SUBTOTAL(103,B:B)

If this post helps click Yes
---------------
Jacob Skaria


"Susan" wrote:

hi all
in column A i have some text data. some of the rows have been
hidden. i want to count how many are left (visible). but any COUNT
functions count the hidden rows, too. any ideas?
thanks a lot in advance.
susan