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(D1:D351)), 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.

Try


=SUBTOTAL(3.D1:D500)


--


Regards,


Peo Sjoblom


"Jen" wrote in message
...
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(D1:D351)), 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?

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,D3:D500)
or
=SUBTOTAL(3,D3:D500)

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

On Oct 25, 6:36 pm, Jen wrote:
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(D1:D351)), 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?

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

On Thu, 25 Oct 2007 10:36:06 -0700, Jen wrote:

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(D1:D351)), 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?

Thanks for all these responses - works great!

"Gord Dibben" wrote:

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

On Thu, 25 Oct 2007 10:36:06 -0700, Jen wrote:

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(D1:D351)), 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?