 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / New Users / February 2008How to count the number of non blank cells in a filtered list
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 TRy the Subtotal function, e.g.,: =SUBTOTAL(102,A2:A10)  SignatureJim | There must be a way of doing this, but I cannot see it. | [quoted text clipped - 18 lines] | | Martin 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 >There must be a way of doing this, but I cannot see it. > [quoted text clipped - 18 lines] > >Martin 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 > Why do say "only works on the first 1000 rows"? > [quoted text clipped - 45 lines] >> >>Martin 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 >Thanks for the help Jim. Subtotal gave me exactly what I wanted. > [quoted text clipped - 74 lines] >>> >>>Martin 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 > Martin > [quoted text clipped - 95 lines] >>>> >>>>Martin 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 >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 [quoted text clipped - 109 lines] >>>>> >>>>>Martin 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 > Maybe your blank cells are not blank? > [quoted text clipped - 132 lines] >>>>>> >>>>>>Martin |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.