Auto Filter + subtotal

How I can add a subtotal/total line below a list with auto-filter ?
0
Alex4922 (316)
7/24/2005 6:19:09 PM
excel.misc 78881 articles. 5 followers. Follow

7 Replies
415 Views

Similar Articles

[PageSpeed] 49

=SUBTOTAL(9, B2:B200)
but the subtotal must be seen either by including a value that the
filter will allow to be displayed, or by being outside the filtered rows.

-- 
---
HTH,
David McRitchie, Microsoft MVP - Excel    [site changed  Nov. 2001]
My Excel Pages:  http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page:        http://www.mvps.org/dmcritchie/excel/search.htm

"alex" <alex@discussions.microsoft.com> wrote in message news:60A1F9E0-2ACB-414D-8154-91EB224223A3@microsoft.com...
> How I can add a subtotal/total line below a list with auto-filter ?


0
7/24/2005 7:26:03 PM
And remember that =subtotal() will ignore those values hidden by the autofilter.

You may want to dump the autofilter and (if your data is sorted nicely) use
Data|Subtotals.

alex wrote:
> 
> How I can add a subtotal/total line below a list with auto-filter ?

-- 

Dave Peterson
0
petersod (12005)
7/24/2005 7:58:46 PM
thanks a lot. i was putting the SUBTOTAL inside the filtered range, so it was 
disappearing...



"David McRitchie" wrote:

> =SUBTOTAL(9, B2:B200)
> but the subtotal must be seen either by including a value that the
> filter will allow to be displayed, or by being outside the filtered rows.
> 
> -- 
> ---
> HTH,
> David McRitchie, Microsoft MVP - Excel    [site changed  Nov. 2001]
> My Excel Pages:  http://www.mvps.org/dmcritchie/excel/excel.htm
> Search Page:        http://www.mvps.org/dmcritchie/excel/search.htm
> 
> "alex" <alex@discussions.microsoft.com> wrote in message news:60A1F9E0-2ACB-414D-8154-91EB224223A3@microsoft.com...
> > How I can add a subtotal/total line below a list with auto-filter ?
> 
> 
> 
0
Alex4922 (316)
7/25/2005 12:14:02 PM
I am using Advanced Filter and subtotal. I am using the function 
Subtotal(9,A1:A22)

Once I change the filter criteria, I had thought, subtotal will 
automatically calculate, but in my case it is not. However, if I double click 
on the cell where I have subtotal and hit Enter, it updates the calculation.

Is there something that I am missing and if you know a fix for this I would 
really appreciate any help.
Thanks,
-- 
Anand Chhajed


"alex" wrote:

> How I can add a subtotal/total line below a list with auto-filter ?
0
9/12/2005 7:51:07 PM
Do you have calculation set for automatic?

Tools|Options|calculation tab.

Anand wrote:
> 
> I am using Advanced Filter and subtotal. I am using the function
> Subtotal(9,A1:A22)
> 
> Once I change the filter criteria, I had thought, subtotal will
> automatically calculate, but in my case it is not. However, if I double click
> on the cell where I have subtotal and hit Enter, it updates the calculation.
> 
> Is there something that I am missing and if you know a fix for this I would
> really appreciate any help.
> Thanks,
> --
> Anand Chhajed
> 
> "alex" wrote:
> 
> > How I can add a subtotal/total line below a list with auto-filter ?

-- 

Dave Peterson
0
petersod (12005)
9/12/2005 11:15:22 PM
Yes, that is set for automatic.

Also, there is a tab for Calc Now(F9). Even if you click on that it does not 
calculate.
But, as I said if I double click on that cell, it calculate automatically.
Appreciate your help!

Thanks,
Anand
-- 
Anand Chhajed


"Dave Peterson" wrote:

> Do you have calculation set for automatic?
> 
> Tools|Options|calculation tab.
> 
> Anand wrote:
> > 
> > I am using Advanced Filter and subtotal. I am using the function
> > Subtotal(9,A1:A22)
> > 
> > Once I change the filter criteria, I had thought, subtotal will
> > automatically calculate, but in my case it is not. However, if I double click
> > on the cell where I have subtotal and hit Enter, it updates the calculation.
> > 
> > Is there something that I am missing and if you know a fix for this I would
> > really appreciate any help.
> > Thanks,
> > --
> > Anand Chhajed
> > 
> > "alex" wrote:
> > 
> > > How I can add a subtotal/total line below a list with auto-filter ?
> 
> -- 
> 
> Dave Peterson
> 
0
9/13/2005 1:49:02 PM
Do you see Calculate in the statusbar (bottom left corner) when you filter on
something else?

xl2002 (I think) added an option to turn calculation off on a sheet by sheet
basis.

But this is extremely rare (in my experience).  

What version of excel are you using?

(I don't have a real good guess.)

One thing to try if you're at your wit's end:

Select all the cells
edit|replace
what: = (equal sign)
with: = (equal sign)
replace all

Maybe it'll make the next change calculate correctly.

Anand wrote:
> 
> Yes, that is set for automatic.
> 
> Also, there is a tab for Calc Now(F9). Even if you click on that it does not
> calculate.
> But, as I said if I double click on that cell, it calculate automatically.
> Appreciate your help!
> 
> Thanks,
> Anand
> --
> Anand Chhajed
> 
> "Dave Peterson" wrote:
> 
> > Do you have calculation set for automatic?
> >
> > Tools|Options|calculation tab.
> >
> > Anand wrote:
> > >
> > > I am using Advanced Filter and subtotal. I am using the function
> > > Subtotal(9,A1:A22)
> > >
> > > Once I change the filter criteria, I had thought, subtotal will
> > > automatically calculate, but in my case it is not. However, if I double click
> > > on the cell where I have subtotal and hit Enter, it updates the calculation.
> > >
> > > Is there something that I am missing and if you know a fix for this I would
> > > really appreciate any help.
> > > Thanks,
> > > --
> > > Anand Chhajed
> > >
> > > "alex" wrote:
> > >
> > > > How I can add a subtotal/total line below a list with auto-filter ?
> >
> > --
> >
> > Dave Peterson
> >

-- 

Dave Peterson
0
petersod (12005)
9/13/2005 5:33:04 PM
Reply:

Similar Artilces:

crete a rule to auto move read messages to a specific folder afte.
How do I crete a rule to auto move read messages to a specific folder after it is closed... You can't. Not after it's closed. You can have a rule to move the email as it is received. After reading you can use the move button, which will also close the email for you. -- --itai "Bill Trigleth" <Bill Trigleth@discussions.microsoft.com> wrote in message news:6F88474E-6DDB-4C3D-B287-1D5C02A5D89E@microsoft.com... > How do I crete a rule to auto move read messages to a specific folder after > it is closed... outlook doesn't support automatically running rules ...

Auto Insert Services Rendered into GP from SQL DB
We have a seperate SQL Server database where timesheets or items of work are captured when performed by someone in our organization. Currently a report is run against the SQL database and then someone manually enters in the transaction into GP. We want to automate this. Is there some program that mimics the manual entry whereby I can export the data from the SQL DB? ...

auto list members feature mystery
I've been working on a C++ project using Visual Studio SP6. I added a message handler via Class Wizard and I noticed the automatic list members feature stopped working within the new handler. In fact, the auto list members feature doesn't work at all below a certain function. Class Wizard has not complaints regarding the handlers and all other functions within the view implementation. Nevertheless, I rebuilt the .clw file just for fun. I'm hoping someone else has seen this bizarre behavior and has a remedy. Thanks! >I've been working on a C++ project using Vis...

There is any auto email format?
Hi All, I know some one can help me in this regard, that I am using excel for Insurance expiry date list more then 2000 list I have. Now what my question is everyday I can’t able to find every expiry date. I want some solution from that if there is any formula like if insurance date was expired how can I get the email for me? Like the date is 8/3/2010 and number is 123456 at the same day automatically I want email from for warning so that I can renewal the same. If there is any formula or something pls help me. I am very struggling for this. Have you tried Conditional ...

Report based on form filter?
Hello, I have a form with Multiple fields filter based on and event procedure where I can filter multiple combo fields. I would like to print a report after the filtered fields, but not sure how to like what is the control source etc.. Any help is appreciated. Here is my sample form filter event procedure. Private Sub cmdFilter_Click() Dim strWhere As String Dim lngLen As Long If Not IsNull(Me.cboMach) Then strWhere = strWhere & "([Mach] = '" & Me.cboMach & "') AND " End If I...

Auto Accepting Appointments
Can Outlook be set to auto-accept appointments through a rule. If so, would this be a 'server' or 'client' rule? If this can't be done, can a setting be changed on the exchange server to mark appointments as "busy" on a user's calendar instead of tentative? Thanks! Stars The auto-accept setting is a client setting not a rule thing "Stars" <smanfredi@waddell.com> wrote in message news:1168538625.174752.273200@i39g2000hsf.googlegroups.com... > Can Outlook be set to auto-accept appointments through a rule. If so, > would this be a ...

Inventory items should auto fill description in Req mgmt
Inventory items should auto fill the description field in requisition management but only if the description field is blank. ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the button, follow this link to open the suggestion in the Microsoft Web-based Newsreader and then click "I Agree" in the message pane. http://www.microsoft.com/Businesssolutions/Community/NewsGroups/dgbrowser/en-us/default.mspx?mid...

auto-updating the auto-updater so 12.0.1 can be installed forces a complete delete and reinstall of office 2008
Version: 2008 Operating System: Mac OS X 10.5 (Leopard) Processor: intel Okay - I must confess I never thought that the mac geeks that work for microsoft had any affinity for writing bad code, but i definately found some in the auo-updating system - which after a reinstall didnt recognise that 12.0.1 OR its own update were ready for download - i had to download 12.0.1 as a .dmg and have NO idea how to force the upgrader program to upgrade itself like its supposed to -- because it DID SO ONCE, and as of this moment none of the applications that are updated by that package refect that they have...

Junk filter for mulitiple inboxes
I have 2 exchange inboxes open in outlook 2003. The junk filter works for the main inbox but does not for the second inbox. Is there any way to do this or is it "not a feature"? Thanks, Jim Not possible as Cached Exchange mode need to be enabled for the Junk E-mail Filter to work and Cached Exchange mode can only be enabled for the default mailbox. -- Robert Sparnaaij [MVP-Outlook] www.howto-outlook.com Tips of the month: -Properly back-up and restore your Outlook data -Creating a Permanent New Mail Desktop Alert in Outlook 2003 ----- "Jim Moraseski" <jsm@mo...

Outlook e-mail filter wildcards?
Does the outlook e-mail filter search for literal keywords only or is it versatile enough to handle wildcards? (i.e., "*refinan*" vs "refinance") I ask because now when I examine the source of my e-mails, spammers are beginning to use tags invisible to the reader "<!--" and "-->" to break up every word in the entire e-mail to defeat the Outlook filter. Not having wildcard flexibility is very frustrating. --Chuck ------------------------------------------------ ~~ Message posted from http://www.OutlookForum.com/ ~~ View and post usenet messag...

Outlook 2003 junk email filter -stopped working!
I use MS outlook for emails and the junk email filter had been working on its own -- blocking junk emails and placing them in Junk E-mail folder. Recently it has simply stopped working! The only change I can think of that I made, was to add many more addresses to the blocked sender's list. I have the following setting, but I still receive emails from the addresses in blocked sender's list, while the junk email folder remains empty: * Level of protection set to high * Permanently delete junk emails instead of sending them to junk email folder is UNchecked Any suggestions on what migh...

prevent public Meeting Request auto-added to User Calendar
In this small office I set up common resources/rooms as calendars in public folders. Users are Authors by default. When a User creates a meeting request in one of these calendars, and another User opens the item, it gets automatically added to that User's personal Calendar, even if they are not an invitee, even if they close it immediately without accept/decline/tentative. I realize this is probably expected behavior. I tried turning off "Process requests and responses" but it still happens. Is there any way of preventing this from happening, especially without impactin...

Auto Pareto Chart
I have a typical Histogram showing the number of incidents that occured by type. I am trying to create an automatic action for the user that re-sorts the data in the chart and reflects in the chart in a decending method. I can do this using a pivot table with no problem but the pivot table requires the user to perform a refresh....I do not want the to have to perform any operations to update the chart if possible. I tried some VBA code to fresh the Pivot table based on the internal clock but this ended up causing some other problems. I am open for any suggestions and can email original data i...

Auto start up with non-xls files
When I load Excel (either from Start of double clicking on an xls), Excel starts up and loads a number of things automatically; some database scripts, exchange files, and one of two others (none are .xls). My guess is that all these files (and perhaps Excel) were open at a time when I had to boot the computer, and something is now telling Excel that these files need to be automatically loaded. Is there someway I can tell not to load these? Thanks, Mark If you have Excel open Go to Tools | Options | General | In the bottom half of the tab open there will be a line that says "At st...

SPAM bypassing our SPAM Filter.
Hello we are encountering instances where SPAM is bypassing our SPAM filter service and going directly to our Exchange server. Our MX record shows the following: mycompany.com. MX IN 86400 smtp1.mke.securence.com. Where Securence.com is a company that is doing SPAM filtering for us, and this mail is bypassing their server completely and going directly to our Exchange server. How can I protect against this? Is there a way to lock down our Exchange server to only accept mail from smtp1.mke.securence.com and would we want to do this? Thanks ahead of time for your as...

Excel XP Subtotal method not working
We have a workbook that was created in Office 2000 that contains a macro that performs subtotals on a range. We have several machines in our company that are running Office XP (various OS) and are having problems with this workbook/macro. In Office XP, the subtotal method does not work. Before adding code to the macro to stop screen updating, the spreadsheet would lock up. Now this doesn't happen but the subtotal function is not working properly. This happens on each Office XP machine. Thanks in advance. Stephanie I had the same problem with my macros when i transfer them from exce...

Activities date filter
I am trying to sort the Activities view by date Next 7 days, Tomorrow, etc. but am getting items that have been scheduled in the past i.e. Next 7 days view shows items that occurred yesterday? How can I get the date filters to only display activities that will occur in the future? Thanks. You can create a custom view for yourself using the advanced find and then saving that filter as a view. If you want the view to be company or team wide then you can do it through creating a "New View" in customizations>Activities. The view would be selected in the View pulldown rather th...

junk mail filter and rules
I utilise various rules to sort various email accounts in to various email folders, for example sales@mydomain goes into my mydomain folder while sales@another goes into my another folder... all pretty straight forward and easy... However I am finding that by utilising message rules to do this, it in effect makes the junk mail filter obsolete in that it looks as if I am somehow by passing it and all SPAM is being directed into these various folders without the Junk Email Filter removing all the SPAM Is there anyway to stop this and make it so that email messages come in, go through the junk ...

Create filtered dataset from XML file?
I need to create a Dataset and datatable from an XML file. The only way I know how to make a Dataset and Datatable, is by using an Access database as my datastore: You know, the usual thing in all the books; an OleDbDataAdapter, with a SQL string and connection. How can I do this using an XML file instead of an Access table? I know I can convert an XML file straight into a Dataset. But the dataset always contains ALL the info from the XML file. I want the dataset to be filtered by using some sort of query. A couple years ago I hurt my brain figuring out Xpath. Boy, i'm just not mea...

Auto Respond in Outlook Express
I am trying to create an auto response email in Outlook Express. I have set up the rule so that when messages come in with a certain subject line that it will "Reply with Message". I created a response email and saved it to my hard drive. The problem that I have is that the message that it sends back is in an attachment on the email. Is there any way to get it to respond and have the response text right inside the email that it sends back and not in an attachment? Thanks in advance Jeff J Donahue <anonymous@discussions.microsoft.com> wrote: > I am trying to crea...

disable auto conversion data to date-format when pasting
I have following problem: I copy some data from webpage. the data are in format like this: 31 / 40 28 / 34 21 / 68 47 / 63 34 / 45 26 / 48 11 / 24 16 / 32 when I paste that to excel I get following: 31 / 40 28 / 34 21 / 68 47 / 63 34 / 45 26 / 48 2004-11-24 16 / 32 as I understeand excel by default tries to choose best format for dat I paste. this is really annoying because it in reality changes data paste - 11 / 24 is no longer 11 /24 - it is now 38315 <- the real wa date data is stored and I can no longer get two first and two las numbers as I would be able from 11 / ...

Auto-send pivot table per user
I have a pivot table which is the performance benchmarking per user. Now what I am doing is double click on user name to drill down their performance per job, copy and plaste it on email, type the email address according to address list, and send to them one by one. Is there any built-in function or VBA/ macro to do these steps with one click? Try Ron de Bruin's website, I needed to do something similar, where I had a pivot table and I needed to email each page field of a pivot table to a different email recipient. The website is as follows:- http://www.rondebruin.nl -- Gary Bro...

Filters #5
I have a spreadsheet that has filters. I have used this spreadsheet for a couple of years now. The whole time that I have used it when I filtered something it would tell me how many of each item was available. All of the sudden it stopped telling me the number of filtered items. What can I do to make it come back? Thank you. http://www.contextures.com/xlautofilter02.html#Count -- Regards, Peo Sjoblom "Kile" <Kile@discussions.microsoft.com> wrote in message news:A70AD932-2426-4D46-BD3F-FD3C530CB85D@microsoft.com... > I have a spreadsheet that has filters. I have...

auto-enter will not clear
I'm having a real problem with the auto-entered function on Money 2004. I set up a bill to auto-enter on the day it was due. On my Bills & Deposits box, where it usually shows upcoming bills, it also shows the bill that was auto-entered. However, the bill was auto-entered on Dec 24 and it is now Dec 30 and the thing still shows up there saying that it was auto-entered. How do I clear this old bill off of my Bills & Deposits box on the Money home page? Thanks for the help Louis I believe it shows the last three entered. Enter some more maybe? Don't sweat the small ...

Auto configure client stationery Outlook 2003
Is it possible to configure the stationery used on cli�nt computer through Exchange Server (NB: Small Business Severs 2003's Exchang Server)? I've searched, almost, everywhere, but I can't find a singl article about this. Is it just so obvious or is it just impossible Eather way, I can't imagine an administrator of a large company goin by all the dekstops, configuring the stationery manually. A little ti would be very much appreciated. Using: Server: MS SBS 2003 (incl. ExchangeServer 2003) Clients: WinXP Prof. & Outlook 2003 Joran Spauwen, joranspauwen@hotmail.com, The...