Count displayed rows when using filters

Is there a set of worksheet functions analogous to the COUNTx functions, but that count only data dislayed when using AutoFilter

Solutions involving SUBTOTALS, or specially coded value in various columns are not feasible due to the data sequence and dynamic requirements of the application

Feasible solutions will include worksheet functions and formulae for use within the functions, as well as instructions on (or pointers to instructions on) how to create my own set of functions

Does anybody know why this capability is so elusive in standard Excel

Thanks in advanc
Bobb



0
anonymous (74722)
4/19/2004 7:11:02 PM
excel.misc 78881 articles. 5 followers. Follow

4 Replies
377 Views

Similar Articles

[PageSpeed] 4

Other than VBA, using the SUBTOTAL() function in some respect is the only way
you will solve this one, I think.  That is what it was designed for, so I guess
I'm struggling to see why you would discount it so lightly and then ask for
something else that does the same job.  It allows for arguments that will then
perform a COUNT, or SUM, or AVERAGE etc, to name but a few.  Excel 2003 has
introduced additional arguments that will also allow manually hidden rows to be
ignored also.

-- 
Regards
           Ken.......................    Microsoft MVP - Excel
              Sys Spec - Win XP Pro /  XL 97/00/02/03

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission :-)
----------------------------------------------------------------------------



"Bobby Stiklus" <anonymous@discussions.microsoft.com> wrote in message
news:5153AF8B-9B4B-4D5C-854F-435569C570C1@microsoft.com...
> Is there a set of worksheet functions analogous to the COUNTx functions, but
that count only data dislayed when using AutoFilter?
>
> Solutions involving SUBTOTALS, or specially coded value in various columns are
not feasible due to the data sequence and dynamic requirements of the
application.
>
> Feasible solutions will include worksheet functions and formulae for use
within the functions, as well as instructions on (or pointers to instructions
on) how to create my own set of functions.
>
> Does anybody know why this capability is so elusive in standard Excel?
>
> Thanks in advance
> Bobby
>
>
>
>


---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.659 / Virus Database: 423 - Release Date: 15/04/2004


0
ken.wright (2489)
4/19/2004 9:07:15 PM
Thanks for your response.  It seems that my hop

I did not lightly discount SUBTOTAL.  The fact that the "look and feel" of data...subtotals does not lend itself to the application notwithstanding, my understanding is that it requires both ordered data and pre-knowlege of exactly what you want to subtotal on.  This is not unreasonable considering what the function is intended to do, but it is not what I am trying do.  Additionally, the requirement of ordered data and pre-knowledge of hierarchical level breaks is be nature at odds with the of AutoFilter within this application.  If my understanding is incorrect  or inadequate, I am quite willing to learn.  For example, Is the subtotal function you refer to not the data...subtotals one

It is important to remember that the data are not hierarchical, nor are the user-filtered values at any given time necessarily relational in the strict database sense: to a great exentent the nature of the selections is associative (and relational only in a mechanical sense)

This is a "lets poke around and look at what we've got and see what we find" sort of application.  The data has no specific need to be even orderable, let alone ordered

Ultimately, the last question of my post stands:  why is it so difficult to get Excel to understand that the data set under consideration is not the entire data set of the worksheet, but rather just the subset which is displayed

 
0
anonymous (74722)
4/20/2004 12:21:03 AM
Sorry, that response got away from me before I was quite finished with it

The unfinished sentence should read "It seems that my hope lies in the additional parameters of Excel 2003."
0
anonymous (74722)
4/20/2004 2:01:02 PM
Hi!

If you are in "poke around and see" mode, you might try what I usuall
use.

Set up the Autofilter.
Select a column and click on its column letter.
Ensure that the count/sum/average thing (I don't know what it i
called!!) in the embossed bit of the status bar is set to Count.

It should now tell you how many items there are in your list.
Apply a filter.
The number will now change to the number selected by the filter.
It is reversible, cumulative...

Al

--
Message posted from http://www.ExcelForum.com

0
4/20/2004 5:44:11 PM
Reply:

Similar Artilces:

advanced filter #6
here is the file: http://www.mytempdir.com/935740 First sorry for my english.... I have this code to insert letter "E" in column N of a sheet GAF only if the dates in column B of sheet GAF are into range DATAIN/DATAFIN use for test DATAIN=01/11/2005 DATAFIN =30/11/2005) Now, i want to use the same code to insert "E" in column N if the range of dates is naturally into range DATAIN/DATAFIN but if the value into column H is the same present into column B of sheet CORPORATE. So, insert in column "N" of sheet GAF the letter "E" in cells N2, N49, N50, N51...

Date display in Excel
Format column of cells as Date, display as mm/dd/yy. Date entered into cell, shows up correctly in the text entry field at the top of the screen, but the data on the worksheet displays as "33747", or similar number. Only happening on one workbook. Try tools|options|View tab|uncheck Formulas. Clark wrote: > > Format column of cells as Date, display as mm/dd/yy. Date > entered into cell, shows up correctly in the text entry > field at the top of the screen, but the data on the > worksheet displays as "33747", or similar number. Only > happening on one...

Can SUMPRODUCT be used for entire column?
This formula results in a numeric result: =SUMPRODUCT((A1:A20)*(B1:B20="b")) but this formula results in a #NUM! result: =SUMPRODUCT((A:A)*(B:B="b")) Which means I need to specify the length of the columns, which may grow over time. Any way to do this for the entire column, without having to specify the length of the column? XL07 removed the limitation on array formulas (which SUMPRODUCT is, even though it doesn't require CTRL-SHIFT-ENTER) and entire columns. For pre-XL07, one can use =SUMPRODUCT(A1:A65535, --(B1:B65536="b")) to get all but on...

Using scanner in Word97
I want to scan a picture into word97 using my HP 4370 ScanJet. Could not find option of "From Scanner" under "Insert" --> "Picture". Apparently I must need some sort of Word97 Add-On. What and where is the add-on? Is it on the Office97 CD? Same applys to Excel97. Also, does microsoft sponsor a Word97 / Office97 discussion group? If so, would appreciate a link. "PSRumbagh" <PSRumbagh@discussions.microsoft.com> said this in news item news:39100DC1-A7EE-4679-881D-526BAA386620@microsoft.com... > I want to scan a picture ...

Duplicate Rows
I have an extract from a student information system in Excel that looks like this. Student Class Grade Quarter John Chemistry 70 1 John Chemistry 80 2 John Math 95 1 John Math 100 2 Alice Chemistry 67 1 Alice Chemistry 47 2 Alice Math 88 1 Alice Math 85 2 What I would like is this: John 70 80 95 100 Alice 67 47 88 85 However, since there are hundreds of students, this would be an extreme pain to do by hand. Is there any built-in formula or function in Excel that can do this? What is it that you actually want to do? (The best approach depends on what your desired end r...

Using later version of microsoft access
Hi, I've got access 2000 on my computer. When I go to open a database someone sent me I get an error message : this database is in an unrecognized format. The database may have been created with a later version of microsoft access. Is there any way I can open and use this file (short of upgrading to later version of access)? If you do not have Access 2002 or 2003, ask the person to save it in Access 2000 format for you. -- Allen Browne - Microsoft MVP. Perth, Western Australia. Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at m...

Displaying Scenarios
I have set up a number of scenarios on a worksheet as they relate to different versions of my budget. Rather than having to go in everytime and display each one individuall, I would like to enter a parameter in one cell that will automatically display the right scenarios. Hopefully, using a conditional statement of some kind. Any help would be greatly appreciated. Jeffrey Albaum Controller Jet Moulding Compounds Inc. Ajax, Ontario, Canada How about a worksheet_change event? I created a couple of custom views and then rightclicked on the worksheet tab that held the important cell. ...

Add rows automatically? Accordion
Is there a way to automatically add/show rows that have data? I have a data entry sheet. Then I have a report. The report pulls data from the entry sheet. If there is no data for a specific line/row item, is there a way to automatically hide or not show the row(s) with no data? Thanks Thanks can I have more than one autofilter on a sheet? Sloth wrote: > Use the filter function > Select the data and click on... > Data->Filter->Autofilter > This should make an arrow appear at the top of the data (in the header row). > click the arror and select "Nonblanks"....

making filtering rules?
hi, i am trying to create a rule that goes something like if (site1.com OR site2.org OR site3.gov) AND (agri OR agriculture OR agribusiness) then move to folder X that is, if it is from any of those sites AND has any of those keywords then move it. The problem is i can't seem to figure out the specifying the AND part. Any help/ideas would be greatly appreciated! Cheers -Gaiko ...

Using Company Wide Mail Templates.
Hi, I do not know if i am at the right spot here, or if it is evne possible, but i got the following question. My boss would like me to make sure that every outgoing mail has the same looks. It starts by adding a signature that is the same for everyone, except with ofcourse personalized information. This was easily done by giving everyone a signature. The next question is however, to put the head of our website, also above our mail. This means that every user that sends a mail, the mail will have a nice header, underneath that header, the mail is typed, and then its ended with the si...

Dynamic Range Selection Using VBA
What I'm trying to accomplish is to be able to run a procedure that selects a range based on a number provided in another cell. For example; if the number 10 is in cell A1, then cells A20:A30 would be selected when I run the macro. If the number 6 is provided, then cells A20:A26 would be selected. Not sure where to start, so any help is appreciated. this may do what you want range("A20").Resize(range("A1").Value+1).select -- Gary Keramidas Excel 2003 "TEK" <TEK@discussions.microsoft.com> wrote in message news:DA9FFF99-FC28-...

WLM Beta Junk Mail Filter
The junk mail filters has stopped filtering mail. The address shows in the list of blocked addresses but the mail is not filtered. The filter worked fine in WLM but I don't think it has worked since I loaded the Beta. Where exactly are you looking when you say the address is the same as the one in your Blocked Senders? The From line may not show the true entry, look at the entry in the From field you see after pressing Ctrl+F3 on that message. Gary VanderMolen, Microsoft MVP (Mail) "DickW2" wrote in message news:O7$LTHNMLHA.4084@TK2MSFTNGP05.phx.gbl... T...

Label a chart of counts with other percentage data
Here's the data: Group 2005 2006 2007 LTM Data A 19.4% 22.8% 21.2% 19.9% Profitability A 6 7 7 7 Count B 9.5% 31.6% 30.4% 30.7% Profitability B 2 3 3 3 Count C 22.4% 23.6% 16.6% 17.6% Profitability C 15 16 17 18 Count D 19.2% 20.5% 15.9% 13.7% Profitability D 8 8 9 10 Count I have successfully generated a stacked bar chart that shows the counts per group by year. Now I would like to include a label for each group to show profitability for each group in each year in the 4 stacks. How would I do that? Thanks, --...

how create Quota filter in WIndows 2003 R2 using Script
Hi all I need create quota filter in Server 2003 R2 using vbscript. quota filter should be applied to directories and not by users. I searched information about it on google without success. thank's in advance Daniel Hi Daniel. First you need open your FSRM (File Server Resource Manager), then you create a quota template, you must specify if your quota is "software" (just monitoring, but never deny the user) or "hardaware" (deny users when they use 100% of the quota), you must specify if you want send e-mail to user when this user use...

Coloring a row
I have a spreadsheet and I want to have cells colored from column A to K if cell h is not blank. So if h3 has a date in it I want A3:K3 to be say light blue. This is for Office 2003. I can do it with conditional formating in 2007, but my work place doesn't have 2007. I did use column L and put an if statement to give a true or false in the cell depending on if the cell in col. h was empty or not. Any ideas how to get this to work? Hi John This sort of thing will work in 2003 conditional formating. In Cell A3 go to Format - conditional formattting. Formula is Paste...

Installed Font does not display in Word 2007
Only 6 of of 8 recently installed TTF fonts will display in Word 2007 Are the missing ones variants like Italic or Bold? On Dec 25, 1:28=A0pm, GWHA <G...@discussions.microsoft.com> wrote: > Only 6 of of 8 recently installed TTF fonts will display in Word 2007 ...

Using Publisher 200 with Publisher 2003
How do I covert PUB2000 documents to Pub2003 documents and vice versa? Pub 2003 can open anything, no conversion necessary. Going backward is a bit trickier. File - Save As and chance the file type to a Pub 2000 file. Possible problems can arise if you've used a feature that was not available in the 2000 version and your file size will grew immensely. -- JoAnn Paules MVP Microsoft [Publisher] "nasuco" <nasuco@discussions.microsoft.com> wrote in message news:500C7A7A-4026-434C-8CC2-2DFDB69D81C4@microsoft.com... > How do I covert PUB2000 documents to Pub2003 do...

EMAIL FILTERS
For just over a week now ALL emails sent to me from external sources are going directly to my deleted files yet anything sent through my company network goes to my inbox as designed. I have looked into my rules and find nothing there to direct these emails to be deleted. Any suggestions where else to look? Disable all your rules. Enable them one at a time to find the culprit. It has to be a rule. CASEY wrote: > For just over a week now ALL emails sent to me from > external sources are going directly to my deleted files > yet anything sent through my company network goes to my...

Formula to count the number of different values in a range
I'm looking for a formula that will give me the number of different values in a range. Example: Column A may have five cells that are "4", five cells that are "7", five cells that are "9". Of the fifteen cells that contain data, there are only 3 different values. I'd like to use a formula that will count the number of different values in column A, in this case the result is "3". Thanks, Paul Try... =SUMPRODUCT((A1:A15<>"")/COUNTIF(A1:A15,A1:A15&"")) OR =SUM(IF(A1:A15<>"",1/COUNTIF(A1:A...

Formula to display nearest following Thursday in mm/dd/yyyy format
Hello, I have been reading and trying different suggestions here to no avail. What I need is a formula to calculate the nearest following thursday, and display it in mm/dd/yyyy format. To be clear, I have a column of varying dates. I need a formula to return the next thursday for each of those dates. To illustrate, say I have 05/22/2010, 05/23/201, 05/24/2010, & 05/26/2010 in cells A1 through A4. In cells B1 through B4, I would like to see 05/27/2010, 05/27/2010, 05/27/2010, & 05/27/2010 representing the following thursday. Thank you for your help! BW T...

Freeze the side column/top row & scroll others
what is the function to set (lock in or freeze) the first column and / or top row of a spreadsheet, so the words and numbers remain in the same place as you scroll the other columns and rows. (so you can add more columns..yet keep the main information in the first column/row) Freeze Panes..... In older versions of Excel, it is under Window. In 2007 version of Excel, it is under View. You first select a cell, then activate the command. Excel uses the selected cell's upper left corner to define the freeze point. Play with it. You can also Unfreeze panes that were fro...

Tricky ComboBox / Filter query
Here's one On Sheet1, from A1:A2931 I have dates, every day from 01.01.02 to 31.12.2009 (A1="01/01/2002", A2="02/01/2002", etc). On a Userform I have 2 ComboBoxes: 1 for month, one for year. Is it possible to use these to filter Sheet1 and leave only those dates chosen in the dropdowns (eg, December 2004 only)? TIA Alan ...

How to use different return email addresses
I use outlook 98. I have a mailbox with three aliases. People sending me emails can use any of the three email aliases and the email will arrive in the same mailbox. Accessing the mailbox once will retrieve all messages regardless of which alias was used. If I reply to these emails though, I want the recipient to think that the email has come from the alias that they originally used. What outlook always seems to do is use the email address of the service that is listed first in Tools->Services->Delivery. Is there any way Outlook can be set up so when I reply to a given email, my email ...

How can I count unique values in a query in the report footer 12-16-07
I have a report that gives me the count of the status of individuals . This works fine as long is there is only one record in the query (in my query there is one record per month). When I query 12 months (individuals may appear in various months) it counts each record of an individual. For Example in a query considering 12 months for a widow Jones it may count her 12 times and for a survivor named Smith may count her 8 times: Widows 12 Survivors 8 I would like to add a count in the report footer that will tell me how many unique individuals I have in the report (Example widows: Tot...

how can you add more rows in excel 2000?
Is there a way to add more rows to excel's already 65536 rows? In other words can you expand excel to have more rows then it already has. Impossible, sorry. HTH. Best wishes Harald "Khody" <Khody@discussions.microsoft.com> skrev i melding news:8A9B7CC9-6832-47D4-830C-7DE692D5616F@microsoft.com... > Is there a way to add more rows to excel's already 65536 rows? In other > words can you expand excel to have more rows then it already has. Unfortunately, you can't increase the number of rows. Versions 2002 and 2003 have the same limitation. tj "Kh...