COUNT returning 0?

[Guest]

I have a spreadsheet with several hundred records in Excel 2000. I have
autofilter turned on, and I've filtered by one of my headings. Now I want to
count the rows that are left - should be easy, right?

However, when I use the COUNT function (=COUNT(D1351)), I always get 0.

Since I also want to count the number of rows with one field empty, I also
tried using a COUNTIF on that (in a different column), and it still returns
0.

What am I doing wrong? I've used COUNT lots of times before, but not in this
worksheet. Could there be some setting that's screwing things up?

 

[Peo Sjoblom]

Try


=SUBTOTAL(3.D1500)


--


Regards,


Peo Sjoblom

 

[Pete_UK]

You should use the SUBTOTAL function. It helps to insert a new row
above your headings, so that you can always see it, and then use:

=SUBTOTAL(2,D3500)
or
=SUBTOTAL(3,D3500)

Assuming D is the column in question. The 2 parameter in the first
formula is equivalent to COUNT, and the 3 is equivalent to COUNTA.
They will count only the visible rows after filtering. There are 11
values which can be used with SUBTOTAL to give different functions (eg
9 is equivalent to SUM), and in later versions you can add 100 to the
parameter to exclude rows which have been manually hidden.

Hope this helps.

Pete

 

[Gord Dibben]

For autofiltered row count use the SUBTOTAL function.

=SUBTOTAL(2,range) which is the equivalent of COUNT

=SUBTOTAL(3,range) which is the equivalent of COUNTA

For more see help


Gord Dibben MS Excel MVP

 

[Guest]

Thanks for all these responses - works great!

For autofiltered row count use the SUBTOTAL function.

=SUBTOTAL(2,range) which is the equivalent of COUNT

=SUBTOTAL(3,range) which is the equivalent of COUNTA

For more see help


Gord Dibben MS Excel MVP