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

Hi there! It sounds like you're having trouble using the SUBTOTAL function to count the number of visible rows after applying an autofilter. Here are a few things you can check to troubleshoot the issue:

1. Make sure that the autofilter is actually applied to the range you're trying to count. You can tell if the autofilter is applied by looking for the filter dropdown arrows in the column headers.
2. Check that there are actually visible rows after applying the autofilter. If there are no visible rows, then the SUBTOTAL function will return 0.
3. Double-check that you're using the correct function number for the SUBTOTAL function. The function number 2 is used to count numbers, so it should work for counting rows. However, if you're trying to count non-numeric values, you may need to use a different function number.
4. Try using the COUNTA function instead of SUBTOTAL. COUNTA counts all non-blank cells in a range, regardless of whether they're hidden by an autofilter or not. Here's an example formula you could use: =COUNTA(A5:A200)

Hi there! It sounds like you're having trouble using the SUBTOTAL function to count the number of visible rows after applying an autofilter. Here are a few things you can try to troubleshoot the issue:

1. Make sure that the autofilter is actually applied to the range you're trying to count. You can check this by clicking on the dropdown arrow in one of the column headers and seeing if the filter options appear.
2. Double-check that you're using the correct function number for counting visible rows. The number 2 should work for this purpose, but you can also try using 3 or 9 to see if that makes a difference.
3. Check that there are no hidden rows in the range you're trying to count. Hidden rows won't be included in the count even if they meet the filter criteria.
4. Try using a different range to see if the function works in a different context. For example, you could try counting the number of visible rows in a different worksheet or workbook to see if the issue is specific to your current file.

I hope one of these suggestions helps you get the SUBTOTAL function working as expected! Let me know if you have any other questions or if there's anything else I can help with.

Try this:

=SUBTOTAL(3,A5:A200)

Hope this helps.

Pete

On Jan 17, 12:45*am, DZ wrote:
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

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

On Jan 17, 9:45*am, DZ wrote:
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

2 is COUNT which counts only numerics.

Perhaps 3 which is COUNTA would be better.


Gord Dibben MS Excel MVP

On Wed, 16 Jan 2008 16:45:08 -0800, DZ wrote:

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

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