SubTotal function to return number of rows returns only zero

[DZ]

I am trying to use the SubTotal function to return number of rows as I
autofilter
--
It always returns zero no matter how many rows are visible.

=SUBTOTAL(2,A5:C200)

A5:C200 is the range including the column headings

Any ideas why this is not working

Thanks

 

[Pete_UK]

Try this:

=SUBTOTAL(3,A5:A200)

Hope this helps.

Pete

 

[carlo]

Hi DZ

some questions, otherwise it's hard to know what's wrong:

Does your range has data in it?
COUNT only counts non empty cells!
Why do you use a 3-Column-Range?
That will result in an odd count , unless you use only one column
per row!

Carlo

 

[Gord Dibben]

2 is COUNT which counts only numerics.

Perhaps 3 which is COUNTA would be better.


Gord Dibben MS Excel MVP

 

[DZ]

Carlo

Thanks for your intuitive question.

As I study this, I realize its a bit more complicated than I originally
thought.

I want to have a formula in a cell, return the number of visible records
after I apply an AutoFilter. In other words, I want to return the same value
that I see in the Status bar after I apply the auto filter.

There are multiple columns in the data table. The AutoFilter will be
performed on different combinations of columns. Data cells contain blanks,
Text and numbers.

I am assuming the solution would be to apply a function or custom function
to one of the columns and have it count all visible cells, including blanks
and cells containing text or numbers.

I can't find a function to do this.
SubTotal(3,Range) doesn't count blanks which would occur if I AutoFiltered
on a different column than the column that is being counted.
COUNTBLANK includes invisible cells in the count.

In a nutshell, I need a function or custom function to count all visible
cells in a column: blank, numeric or text.

or

Count the number of AutoFiltered records. Same result

Thanks for any help