Counting only displayed cells in the column

[CEG_Staffer]

I have a worksheet that I would like to be able to count only the displayed
cells in a column i.e. I do not want to count hidden cells (rows). If all
rows are displayed the total count should equal all rows with contents
(counta function), if not - count only those rows displayed.

 

[Otávio Alves Ribeiro]

Hi there.
There are, at least, two choices:

=SUBTOTAL(2,A2:A11) count only the cells which are not hidden by means of
filters.

OR

=SUBTOTAL(102,A2:A11) count only cells which are not hidden either manually
or by means of a filter.

Regards,
Otávio

 

[Peo Sjoblom]

If you have Excel 2003 and later you can use subtotal


=SUBTOTAL(103,A1:A1000)


will count either text or number and only visible cells in A1:A1000, for
earlier versions you would need code to create a User Defined Function (UDF)

Look up help for the SUBTOTAL function if you have 2003-2007 there you can
see all the different things you can count or calculate like sum, average
etc

--


Regards,


Peo Sjoblom

 

[Pete_UK]

If you have Excel 2002 or later, then you can use this:

=SUBTOTAL(103,A1:A100)

Hope this helps.

Pete