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

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

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

=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

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

On Jan 17, 10:07*am, DZ wrote:
=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

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

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

Pete

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



- Hide quoted text -

- Show quoted text -

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)



"DZ" wrote in message
...
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