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

Try this:

=SUBTOTAL(3,A5:A200)

Hope this helps.

Pete

=SUBTOTAL(3,A5:A200)

Hope this helps.

Pete

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

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

2 is COUNT which counts only numerics.

Perhaps 3 which is COUNTA would be better.

Gord Dibben MS Excel MVP

Perhaps 3 which is COUNTA would be better.

Gord Dibben MS Excel MVP

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

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

1. $sth->rows, doesn't return the correct number of rows return

2. If number < 0 return zero else return one

3. If number is 0 return zero else return one

4. R: function - find first number in row > 0, return cell column number

5. function - find first number in row > 0, return cell column number

6. can't return zero when no rows exist using aggregate function sum

7. can't return zero when no rows exist using aggregate function

8. crosstab error 'query-specified return row and actual function return do not...

9. Store Procedure which should return multi rows but Returns only one Row Always?

10. In a column of numbers, how do I return the last non-zero number?

11. How do you return current ROW number to definition in NAME function?

12. Return 2 values (string, number) multiple rows, from java stored function

13. [PATCH 1/2] tracing/function-return-tracer: Make the function return tracer lockless

14. [PATCH 1/2] tracing/function-return-tracer: Make the function return tracer lockless

15. [PATCH 1/2] tracing/function-return-tracer: Make the function return tracer lockless

5 post • Page:**1** of **1**