Count the number of AutoFiltered records

[DZ]

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

 

[Mike H]

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.

Maybe

=SUBTOTAL(2,A2:A15)

This is with your un-filtered data in cells A2 - A15

Mike

 

[DZ]

=SUBTOTAL(2,A2:A15)

This is with your un-filtered data in cells A2 - A15

Thanks for the reply

I tried that. It won't work for a number reasons

1. Subtotal with 2 as 1st arg only counts numeric values, not text, in the
visible rows and it doesn't count blanks. Subtotal with 3 as 1st arg counts
all values but not blanks

I need to count only cells in the visible rows, but every cell in the
visible rows, text, numbers and blanks.

You see my dilemma?

DZ

 

[Pete_UK]

Choose a column where you have data in every cell, then use:

=SUBTOTAL(3,A2:A200)

or whichever column it is that has an entry in every cell (as I
advised in your previous posting). If you don't have such a column,
then use a helper column (eg column G) and fill it with 1 to cover
your data range. Then use this formula:

=SUBTOTAL(9,G2:G200)

Hope this helps.

Pete

 

[DZ]

Pete

Thanks again for the suggestion. Your solution may the only one available
and I know it will work. However, I was trying to find a way to do it by
counting a column that contained blanks too. i'm still looking.

CZ

 

[Pete_UK]

Well, only you know what your data looks like ...

Pete

 

[Bob Phillips]

What is wrong with

=SUBTOTAL(103,A:A)

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)