Count Rows When Using Auto-Filter

Using the auto-filter function I need to count the number 
of rows returned each time I select a new value. I can do 
a COUNTA (–1 to allow for the header row), on the column 
that counts all the rows but when I use the filter I still 
get the total count not a count of the reduced quantity by 
the filter.
Can anyone help?
Nick

0
ner1 (1)
10/6/2003 1:59:52 PM
excel.misc 78881 articles. 5 followers. Follow

1 Replies
679 Views

Similar Articles

[PageSpeed] 0

use the subtotal function with a first argument of 3

=subtotal(3,A2:A300)

as an example.

Make sure the column you do the counta on will have values in all rows.

-- 
regards,
Tom Ogilvy

"Nick" <ner@westnet.com.au> wrote in message
news:15e401c38c12$1d363e60$a101280a@phx.gbl...
> Using the auto-filter function I need to count the number
> of rows returned each time I select a new value. I can do
> a COUNTA (&#8211;1 to allow for the header row), on the column
> that counts all the rows but when I use the filter I still
> get the total count not a count of the reduced quantity by
> the filter.
> Can anyone help?
> Nick
>


0
twogilvy (1078)
10/6/2003 2:38:05 PM
Reply:

Similar Artilces:

how to freeze pane with only one row?
Hello all, I am trying to create a view so that I can see my header row as I scroll down a table with thousands of records/rows. When I select this row1 and then go to Window->Freeze Panes, I get a pane that contains the first 12 rows. I would like just the 1st row to be frozen in this pane. Have tried selecting the entire row and just the columns in the first row that are required - to no avail. Can anyone help me do this? Thanks! symp news, select a cell in row 2 then freeze panes -- Paul B Always backup your data before trying something new Please post any response to the newsgroups...

How to use outlook address in Excel
Hello, I have an Excel sheet which I use as an invoicing-application. I would like to retrieve address-data from Outlook where I keep all my contact-data of my customers. So, I want to select a customer from my Outlook contactlist when I am writing a new invoice in Excel. In Word, I have a macro which does this, but unfortunately the Application.GetAddress does not work in Excel. Can somebody help me ? "Henny Slokker" wrote: > Hello, > > I have an Excel sheet which I use as an invoicing-application. I would like > to retrieve address-data from Outlook where I...

How do you change the rows to columns and columns to rows.
Ok, I am stumped on how to do this? I have a spreadsheed that has Dates in the A column and Data in the row. I want to make is so, the data is in the A column and Dates ru accross in the 1 column. How can I do this? Sound easy, but I am stumped??? :eek -- cc4digita ----------------------------------------------------------------------- cc4digital's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=2656 View this thread: http://www.excelforum.com/showthread.php?threadid=39843 Highlight your data and copy it into the clipboard. Move to an open spot in the sprea...

Hiding and Unhiding Rows.. Unhide does not work..
Version: 2008 Operating System: Mac OS X 10.5 (Leopard) Processor: Intel I use this formating feature quite often. But, on this one worksheet which is a rather large data list (6000 Rows) I can NOT unhide rows that I have previously hidden. It just does not work. <br><br>I have other workbooks open and I have no problem hiding and unhiding in the normal way. <br><br>The workbook is NOT protected. The cells are not locked. But the file was originally a windows file. <br><br>Is there some switch I do not know about? This is possibly a rare occurren...

IE8 uses 95+% of cpu after update to sp3
I just updated to XP sp3 and did updates after that and IE8 is running so slow. I loaded task manager to see what was happening and IE8 was using up to 99%. My system was a slow XP sp2 but did a disk clean and defrag and it was running a lot faster. Apps load and run faster after sp3 but not IE8. Thanks for any suggestions mx5 wrote: > I just updated to XP sp3 and did updates after that and IE8 is > running so slow. I loaded task manager to see what was happening > and IE8 was using up to 99%. > > My system was a slow XP sp2 but did a disk clean and defrag and...

Counting occurences with multiple entries
Hi, I have a spreadsheet with a large volume of data. I want to count how many times the equipment id (column A) comes up with a specific fault (Column B)i.e Fail. There could be numerous entires for the same piece of equipment with different types of faults. I've tried count, countif etc... but cant get the formula to lookup the value and count up the occurences properly. Can anyone help? Thanks. Try =SUMPRODUCT(--(A1:A100="equipment id"),--(B1:B100="Fail")) With equipment id in cell C1 =SUMPRODUCT(--(A1:A100=C1),--(B1:B100="Fail...

advance filters
I would like to know if anybody knows how to use advance filters if you want to select only nubers that begins with 572. For example I want to select all the number in a list that star with 572.... I need to do it with advance filters because I have to use more filters in others columns. Thank you. Manuel Hi Manuel, Perhaps you could add a helper column and filter on the helper column? Assume the relevant data to be in cells B2:B100: Insert a helper C column In Cell C2 enter the formula =Left(B2, 3) = 572 Copy down to C100 Filter on column C using TRUE as the filter condition. -...

8192 rows limit when opening wk1 files ?
Hello, I am opening a .wk1 file, which contains around 60K rows, but only the first 8192 rows appear in Excel. Is there any way to have all the rows in the Excel file ? Thanks, Paul "paul" wrote... >I am opening a .wk1 file, which contains around 60K rows, >but only the first 8192 rows appear in Excel. > >Is there any way to have all the rows in the Excel file ? WK1 as in Lotus 1-2-3 Release 2.x native file format? Such .WK1 files support a single worksheet of 256 columns and 8,192 rows. PERIOD. Did you generate this '.WK1' file using a program other then...

VB: using a string to set a range object?
I'm a bit new to the excel "range" object type. I was suprised to see that while I can do: dim chunk as Range chunk = .Range(A5:B6) I apparently cannot do: dim chunk as Range dim stuff as string string = "A5:B6" chunk= .Range(string) How can I concatenate up a string describing a range, and then use it to define a range object's target cells? - Ross. Oops, I meant chunk = .Range("A5:B6") in the first example - I forgot the quotes. R. "RGK" <nothanks@nospam.go> wrote in message news:RqydnSWzbu_OEZbeRVn-2A@...

Select Rows dependant on certain field data
How can I select certain rows in a spreadsheet based upon the text foun in one column? To explain: All rows have a column that contains tex "immediate" or "ongoing" or "closed". How can I select only the row that contain the word "immediate" in that column? When I say select a mean select as in ready to 'copy' or 'cut' tha data ready to be pasted elsewhere. I know what I mean :confused -- Madd ----------------------------------------------------------------------- Maddy's Profile: http://www.excelforum.com/member.php?action=getinf...

How to use CSplitterWnd for Dynamic Nested Splitter window?
Using VC++ (.NET) I am trying to implement a Dynamic Nested Splitter window using CSplitterWnd. I haven't found any documentation that explains how to get this to work properly and the only examples I have found also do not work properly. There are plenty of examples of Static Nested Splitter windows, but I need an example of a Dynamic one. The static splitter windows won't work for my requirements. Are there any examples or documentation that explains how to properly implement a Dynamic Nested Splitter Window using CSplitterWnd? Thanks, Dave Hi Dave, > Using VC++ (.NE...

Coding Convention for using Binary FlagWords?
Hi all --- I'm thinking of putting the .Tag property to use by using a binary ("bitwise") encoded flag word. Over time I've come across different possible uses for .Tag but have never standardized how I use it. Has someone developed a convention for "parsing" binary flag words? A simple IF works if I only need to test for one flag, but if I need to check for the presence of multiple bits the only construct that comes to mind is a series of IF statements .... something along the lines of: Enum FlagWord FlagA = 1 FlagB = 2 FlagC = 4 .......

Bank Acct Auto Update without sync to MSN
I would like to have my accts auto update without syncing to the MSN website. I would also like to not use the passport password. When I remove the pasport password and create a local password, I no longer am able to auto update from my bank. Do I have to have the MSN passport and web access to do this? If not, how do I do this? I have Money 2005 Deluxe on an Windows XP Pro system. I am creating a new money file so I can wipe clean and try anything new. thanks!! In microsoft.public.money, fire_317 wrote: >I would like to have my accts auto update without syncing to the MSN we...

Using Outlook client for CRM 3.0 in a remote office
I need to set up access to the corporate CRM from several remote offices. All of them have VPN connections (Windows or Checkpoint). What are the steps required to configure remote clients to be able to use CRM features in Outlook. Remote users can connect to CRM through Internet Expolorer. Thanks. Assuming they are connected, the isntallation over a VPN connection should not be any different than a typicaly installation. If the connection is slow though, the first synch & Go Offline process will be noticiably slower though. -- Matt Parks MVP - Microsoft CRM "mkatsev"...

using std::deque in multiple threads
I have a thread that listens on a message queue and populates a std::deque with events from that message queue, but only holds the latest 100. In my main thread, I want to populate a listbox with the information from that deque. So, it ends up being that one thread can add/remove items from the deque while another thread is trying to iterate through them. How can I make this thread-safe? Thanks, PaulH The code looks a bit like this: std::deque<MESSAGE_TYPE> m_dequeMessages; CMyClass::MessageThread() { //... while (ReadMsgQueue(hMsgQueue, &msg,...)) { m_dequeMessages.pu...

Wordwrapping a long sentence using F2, how to end the process?
Using Excel 2002...I pasted in a long sentence, and it goes off the right side of the screen. I know that I can make it word wrap by hitting F2. I've done that and it works nicely. But I was told that I could end this process by hitting Alt-Enter, and that doesn't work... all that happens after Alt-Enter is a blank new row opens up below the word-wrapped rows. If I hit Alt-Enter again, another blank row opens up below. How can I end this F2 word-wrap and go on with other business. John alt-enter is used to force a new line in a cell. If you want to wrap the text a...

Use a wildcard within edit/replace
Hello Is there a method of using a wildcard function within edit/replace (in Excel 2003) so that I can tweak the way a formula works? The example I have is the following formula =IF(ISERROR(VLOOKUP($A8,DataImport!$A:$J,5,FALSE)),"",VLOOKUP($A8,DataImport!$A:$J,5,FALSE)) which I would like to change to =IF(ISERROR(VLOOKUP(TEXT($C8,"0000),DataImport!$D:$E,2,FALSE)),"",VLOOKUP(TEXT($C8,"0000"),DataImport!$D:$E,2,FALSE)) The issue is (I think) that this formula is repeated many times over in one column over a number of worksheets - therefo...

excel 2000 message
excel 2000 message - 'cannot use object linking and embedding' Were they hit by the MSBlast worm? One poster (Lutz Meyer) guessed that this was the cause of his problems. I haven't seen any confirmation/denial, but you may want to read his post: http://groups.google.com/groups?threadm=3F3971AF.FA4490F5%40msn.com Post back with your results. I'm curious if that was the problem. (It's come up quite a few times since MSBlast hit.) bill bootle wrote: > > excel 2000 message - 'cannot use object linking and > embedding' -- Dave Peterson ec35720@msn.c...

Mobile using up CPU
Hi there Since we installed CRMMobile our (new) server has been SO SLOW! SqlServer is taking anywhere from 69 - 99% cpu time (mostly around 99) I have run sql profiler and discovered that many times a second the following transactions are running: exec sp_executesql N'SELECT recipientAddr,recipientSrvcPort,sourceAddr,sourceSrvcPort, MIN(arrivalDate) as minArrivalDate, count(*) as messageCount FROM vwInBoundQueue WHERE recipientAddr=@recipientAddr AND recipientSrvcPort=@recipientSrvcPort GROUP BY sourceAddr,sourceSrvcPort,recipientAddr,recipientSrvcPort ORDER BY minArrivalDat...

Auto Complete
Been racking my brain for this one but cant think!! I have a form with 'Account Ref' as one field (and a company name field) which is a lookup from a master table which has account ref, customer name, address.etc etc etc. the lookup returns account ref and company name. Basically i want to only return the account ref in the account ref field and then auto populate the company name field. im not hot on sql so if you could talk me though that would be grande!! thanks in advance for any assistance. It depends why you are doing this. If you want to auto populate bound fields (fi...

File in use .... is locked for editing
Please help. In Microsoft Excel 2000, we seem to be getting the above error message frequently. No other users are in the file, and there aren't any temporary files relating to the file in question. Any ideas on what is causing this? We are running a mixture of Windows 2000 & XP machines on to a Small Business Server 2000. Hope to hear from someone soon Regards Simon I think you killed the usual answer of cleaning the temp folder. But have you rebooted? Maybe there's a hidden instance of excel running that "owns" the file. And if the file/workbook is on a net...

how can i start using excel for the first time?
i cant figure out how to get excel to work for me and im a first time user of it? i Cant get nothing to work on it? can anyone please help me here Paul Can you get Excel to start up? Can you get a blank workbook to open via File>New? For basics on Excel see.......... http://www.usd.edu/trio/tut/excel/index.html http://www.baycongroup.com/el0.htm Microsoft Training Courses. http://office.microsoft.com/en-us/training/CR061831141033.aspx Gord Dibben Excel MVP On Wed, 15 Dec 2004 15:39:02 -0800, "Paul Scheffer" <Paul Scheffer@discussions.microsoft.com> wrote: >i ...

Can I change default workplace "All" filter to "Appointments"??
Hi all, We heavily use the Appointments section of the CRM Workplace, and have defined a number of important Appointment views (Today's Appointments, Tomorrow's Appointments, To Be Scheduled Appointments, Waiting for Callback), etc. (We heaviy customized Appointments to add a status value, and links to our custom entities. Very useful!) The views all work fine, except they don't show up in the main set of activity views. They DO show up when the Type = "Appointment", so using these views require the user to: a) Click on Workplace b) Change the Type from All to Appoin...

Counting Blank Cells in Pivot Table
How do I have a Pivot Table count the blank cells as well as the other cells? The Pivot table I created counts everything that had data (A, B, C, D, etc...) but does not count the blank cells. Thank you, Jack As answered in microsoft.public.excel.charting: To count the blanks you could use a space character, or the formula ="", in the source data, instead of leaving the cells blank. The pivot table will be able to count those. Jack wrote: > How do I have a Pivot Table count the blank cells as well > as the other cells? The Pivot table I created counts > everythi...

Specify data for use in FRx pivot tables
How do I specify what data fields appear in an Excel pivot table (or ..cub file) from FRx? I want to export the natural account number into the pivot table to sort on. TIA ...