How to count the number of non blank cells in a filtered list

[Martin C]

There must be a way of doing this, but I cannot see it.

I have a spreadsheet with items to be progressed. This list can be filtered
on a number of criteria. The filter currently only works on the first 1000
rows. It is not considered likely that it will ever go above this.

When I filter, Excel tells me there are "x of y records found" where x is
all the filtered items plus any blank rows left until row 999; and y is the
number of records in total (minus any header rows).

How can I get Excel to just give me the number of filtered rows (minus the
blank ones).

What I am basically after, is the value displayed in the bottom right of the
sheet if COUNT is selected and the data highlighted. (The one where SUM is
the default.)

Hope I have made myself clear.

TIA

Martin

 

[Jim Rech]

TRy the Subtotal function, e.g.,:

=SUBTOTAL(102,A2:A10)

--
Jim
| There must be a way of doing this, but I cannot see it.
|
| I have a spreadsheet with items to be progressed. This list can be
filtered
| on a number of criteria. The filter currently only works on the first 1000
| rows. It is not considered likely that it will ever go above this.
|
| When I filter, Excel tells me there are "x of y records found" where x is
| all the filtered items plus any blank rows left until row 999; and y is
the
| number of records in total (minus any header rows).
|
| How can I get Excel to just give me the number of filtered rows (minus the
| blank ones).
|
| What I am basically after, is the value displayed in the bottom right of
the
| sheet if COUNT is selected and the data highlighted. (The one where SUM is
| the default.)
|
| Hope I have made myself clear.
|
| TIA
|
| Martin
|
|

 

[Gord Dibben]

Why do say "only works on the first 1000 rows"?

Autofilter does not count blank rows below your data range.

i.e. data in rows 1 to 500

Select A1:A1000 then autofilter on a value.

The status bar will read x of 500

Not x of 1000

The Count you see on the status bar counts the visible cells only after a
filter.

Depends upon how many columns you have.

If one column the Count will display numbers of visible cells + 1 for header.

If two columns the Count would be 2 x visible cells + 2 for headers.


Gord Dibben MS Excel MVP

 

[Martin C]

Thanks for the help Jim. Subtotal gave me exactly what I wanted.

Gord, I think I may have been a little vague in my description - so to
clarify for anyone else reading this thread.

I had limited the range of the autofilter to only include the first 1000
rows. That is why I had stated that it only works on the first 1000 rows.
With this setup, the autofilter includes all of the rows in the autofilter
range (including the blank ones that come after the end of the rows that
actually contain data).

After a little experimentation, I noticed the problem that had caused my
initial post.

If I filter on a value in a column, then the status bar will give the
correct number of records found, exactly as you said. However, for one
filter I use a lot, I have a custom filter which checks for "does not
contain" "closed". This gives the problem I have stated. It counts the
number of records in the appropriate column which contain data, as well as
the blank ones. Obviously, it is counting the blank as "does not contain"
"closed", so the status bar gives a misleading value - ish.

I have used Jim's Subtotal to give me what I need and display it in a cell
at the top.

Thanks to you both.

Martin

 

[Gord Dibben]

Martin

I cannot replicate this on my 2003 version.

Select a1:a1000 which includes a1 as header and a2:a500 with "closed" and 500
blanks cells A501:A1000

Filter on "does not contain" "closed"

I get 0 of 499 records.

Excel's filter range extends only to the last contiguous non-blank cell.


Gord

 

[Martin C]

Cannot explain that! I also have Excel 2003. I have tried changing it to
"does not equal" "closed" and get the same result. I even changed my data so
that all the used rows had Closed in the appropriate column, ran the filter
and still had the same problem. Weird.
The formatting for the colum in question is set to General. Any filter I do
give me x of 999 records found.

Maybe my Excel is corrupted somehow. I may try getting the company to
reinstall it some time later.

Thanks for your help.

Martin

 

[Gord Dibben]

Maybe your blank cells are not blank?

Do they have formulas that return a blank?

Maybe a space in?

In a cell adjacent to a blank cell enter =LEN(cellref)


Gord

 

[Martin C]

I tried the LEN check, and the cells are indeed blank (ie len=0).

I even tried removing all of the conditional formatting (which is applied to
an entire row if appropriate cell in the column in question is set to
'Closed'. This had no affect on the problem either.

The only other thing I can think of is that the data placed into the area in
question is copied into the relevant columns via a macro which pulls out the
relevant data from another area of the sheet. i.e. Data is obtained from a
seperate application, pasted into a particular area in the sheet and the
macro run to process the data. I don't see why pasting data in and then
filtering should have any effect on the problem, but I am out of ideas or
possible information to give you.

Thanks for your help

Martin