Growing Total using Autofilter in Excel?

Hello,

I have a spreadsheet i am working on in Excel 2002. I have a ton of
data which i am sorting to date using AutoFilter, which is working
fine. when the data is not filtered i have a rolling total which adds
up the whole thing like this.

a b
1 100 100
2 100 200
3 150 350

but when i use the filter i see the larger totals in the list (which i
understand) What i want is the same thing in a new column, but for
only the current data from the view. example

a b
~
7 150 150
8 100 250
9 200 450

I have tried using sum and subtotal but it doesnt allow + example:
=sum(b7+a8) and =subtotal(9,b7+a8) I know the last example is illegal.

Does anyone know how to do this ?

0
sdhunter9 (1)
5/11/2007 4:34:14 PM
excel 39879 articles. 2 followers. Follow

3 Replies
558 Views

Similar Articles

[PageSpeed] 16

I would copy the Autofiltered data over to a helper sheet and add the formula 
there....

Vaya con Dios,
Chuck, CABGx3



"sdhunter9@gmail.com" wrote:

> Hello,
> 
> I have a spreadsheet i am working on in Excel 2002. I have a ton of
> data which i am sorting to date using AutoFilter, which is working
> fine. when the data is not filtered i have a rolling total which adds
> up the whole thing like this.
> 
> a b
> 1 100 100
> 2 100 200
> 3 150 350
> 
> but when i use the filter i see the larger totals in the list (which i
> understand) What i want is the same thing in a new column, but for
> only the current data from the view. example
> 
> a b
> ~
> 7 150 150
> 8 100 250
> 9 200 450
> 
> I have tried using sum and subtotal but it doesnt allow + example:
> =sum(b7+a8) and =subtotal(9,b7+a8) I know the last example is illegal.
> 
> Does anyone know how to do this ?
> 
> 
0
CLR (807)
5/11/2007 4:54:01 PM
Instead of using =SUM(A1:A1000), use =SUBTOTAL(9,A1:A1000) -- it will sum 
only the rows which are showing because of the filtering!
Bob Umlas
Excel MVP

<sdhunter9@gmail.com> wrote in message 
news:1178901254.410485.310590@u30g2000hsc.googlegroups.com...
> Hello,
>
> I have a spreadsheet i am working on in Excel 2002. I have a ton of
> data which i am sorting to date using AutoFilter, which is working
> fine. when the data is not filtered i have a rolling total which adds
> up the whole thing like this.
>
> a b
> 1 100 100
> 2 100 200
> 3 150 350
>
> but when i use the filter i see the larger totals in the list (which i
> understand) What i want is the same thing in a new column, but for
> only the current data from the view. example
>
> a b
> ~
> 7 150 150
> 8 100 250
> 9 200 450
>
> I have tried using sum and subtotal but it doesnt allow + example:
> =sum(b7+a8) and =subtotal(9,b7+a8) I know the last example is illegal.
>
> Does anyone know how to do this ?
> 

0
bobumlas (25)
5/11/2007 7:55:43 PM
With headings in row 1, add this formula to cell C2, and copy down:

   =SUBTOTAL(9,B$1:B2)



sdhunter9@gmail.com wrote:
> Hello,
> 
> I have a spreadsheet i am working on in Excel 2002. I have a ton of
> data which i am sorting to date using AutoFilter, which is working
> fine. when the data is not filtered i have a rolling total which adds
> up the whole thing like this.
> 
> a b
> 1 100 100
> 2 100 200
> 3 150 350
> 
> but when i use the filter i see the larger totals in the list (which i
> understand) What i want is the same thing in a new column, but for
> only the current data from the view. example
> 
> a b
> ~
> 7 150 150
> 8 100 250
> 9 200 450
> 
> I have tried using sum and subtotal but it doesnt allow + example:
> =sum(b7+a8) and =subtotal(9,b7+a8) I know the last example is illegal.
> 
> Does anyone know how to do this ?
> 


-- 
Debra Dalgleish
Contextures
http://www.contextures.com/tiptech.html

0
dsd1 (5911)
5/11/2007 8:24:54 PM
Reply:

Similar Artilces:

Excel Opens a Blank Workbook
Every time I launch and excel file it also opens a blank workbook along with that file. Why is this happening? Look in the Excel help for "Customize how Excel starts" -- Regards Ron de Bruin http://www.rondebruin.nl "Alison" <anonymous@discussions.microsoft.com> wrote in message news:687FD6EA-0AFB-4899-B5EA-188A19528872@microsoft.com... > Every time I launch and excel file it also opens a blank workbook along with that file. Why is this happening? ...

Code for making Excel beep
Hi I'm not sure how to alter the code below to achieve what i want. I have the following code that i run at the end of a macro,which give an audible beep if conditions are met. If Range("B2").Value > Range("C5").Value And Range("C5") > "" Then xcount = 1 For xcount = 1 To 5 Beep newHour = Hour(Now()) newMinute = Minute(Now()) newSecond = Second(Now()) + 1 waitTime = TimeSerial(newHour, newMinute, newSecond) Application.Wait waitTime Next End If End Sub Below is part of a formula that outlines the new conditions that nee to be met. I...

Merged cells when exporting reports into excel files
The excel files exported by custom reports we have made using reporting services, have a lot of merged cells. Do you have any idea why does this happen ??? Hi George, We have experienced this before and would like to share our findings Check your report to see if you have any labels added to the report... Generally Report Headings that are wide enough to extend the width of the columns below... Fix is to get to the width of the label to be equal to the width of the entire column being merged. So say you find column D and E merged. Extend the report header label to have a width wide e...

How to querry data or can I use SQL ?
Hi, I have one two worksheets in Excel one is simple and the second is pivot table, what i want to do is: I want to do something what would be in SQL like: SELECT averange_field_from_pivot_table WHERE field_from_Worksheet1 = field_from_Worksheet2 How can I handle this in excel ? regards Peter screw Excel, use Access grow up "Piotr" <hokah@wp.pl> wrote in message news:cq47bi$sql$1@inews.gazeta.pl... > Hi, > I have one two worksheets in Excel one is simple and the second is pivot > table, what i want to do is: > I want to do something what would be in SQL like...

Getting excel macro to prompt for a file
I currently have a macro that opens a file called "a" (no extension), as shown below: Workbooks.OpenText Filename:="ftp://xxx.xx.xxx.xxx/home/spool/a", _ This file is not always going to be "a" though. Is there a way i can get excel to show this folder, so that i can manually select a file, and for it to continue the macro afterwards? many thanks Neal On Sat, 6 Dec 2003 08:33:43 -0000, "NealUK" <xx@xxx.xx> wrote: >I currently have a macro that opens a file called "a" (no extension), as >shown below: > >Workbooks.OpenTe...

Outlook 2007
Can anyone help? I can create a template, no problem. I can reply to emails, no problem. What I can not do and for the life of me, I'm not sure Outlook does either, is reply to an email using a template. It seems so simple, Reply, Reply To All, Reply Using Template ... In Outlook 2003, one had to create a custom rule. In Outlook 2007, the feature has been removed. Any ideas on how I can reply to an email with a specific template? I use templates in Outlook 2007. Check here it may be helpful: http://office.microsoft.com/en-us/outlook/HA102487481033.aspx / Andr...

Data hidden/lost in Excel 2003 spreadsheet?
I have an odd situation I was hoping some of you excel experts might be able to help me with. I was sent a spreadsheet (task schedule list) that was about 25kb (only 40 rows by 8 columns). After I did some editing (adding 2 new columns and adding some information to them) and saved the file, I noticed the file size has ballooned up to 2MB! I went hunting through the file to see if had accidentally pasted a something huge from my clipboard but could find nothing. I know it is the column I added as when I delete the entire column and save the file it goes back to the original 25kb. But w...

Automatically resize excel charts when exporting them to PowerPoin
The message below is in response to a solution for my question of whether it is possible to build a macro that will export multiple Excel charts to a PowerPoint presentation with one chart to a slide. The "responder" gave me an ingenious solution that works perfectly. My next question is if those charts can be automatically resized. Keep reading for details. Jon, This is working brilliantly. I'd like to throw one more challenge at you if you don't mind. Is there a way to output the charts to PowerPoint with the following parameters: Height 5.66 inches Width 9.66...

Can Excel find a cell based on two criteria?
My worksheet does not have column headings, but I do have unique data in a particular row. The data is a Date. The rows contain unique entries also (2 characters, always unique example JO,WO,XX,RR,TV ETC..) Is there a way, to have excel find the cell that would be in the column that contains a date (entered by user)and the Row that contains the initials (again entered by the user)? If so, can a userform be created for the user to enter the information (date mm/dd/yy) + (XX), along with the New data that goes into the 'intersecting' cell? c d e ...

Search within folder name using VBA
Hello, I have a folder structure in Windows Explorer, every folder has its own customer number. The user should be able to type a substring of a customers number in an Access form and I want search within the folder names and return the folder paths. How can I search within the folder names?? i found many articles about searching files but none of folder names. I'm very happy about every suggestion. Thank you, Walter ...

Paste Excel
Everytime I try to copy a table from Excel (2007) to an Outlook e-mail everything looks fine on the screen but when the message is sent the text of the copied table is bold and of a different font that is barely readable. The e-mails are html and in Excel I use the Paste/As Picture/Copy as Picture/As Shown When Printed option. Have also tried the As Shown On Screen option As Picture and get the same results. When I do the same in Word the paste seems to work fine. What's the best way to insert an Excel table into an Outlook document? ...

Anyone developed an Excel sheet for Cost of Quality?
In article <F4F2EC77-E21A-4CFA-9713-8E1F5BBAA802@microsoft.com>, DoctorD@discussions.microsoft.com says... > > Probably. Searched Google? -- Regards, Tushar Mehta www.tushar-mehta.com Multi-disciplinary business expertise + Technology skills = Optimal solution to your business problem Recipient Microsoft MVP award 2000-2005 ...

iSCSI or SMB using SBS 2003
Hi, I need some advice. I've got a Thecus N8800Pro Storage Applicance, and a Windows SBS 2003 Server. I want to hold my important business files on the applicance so that it can be accessed by the other users of the network. I tried this by connecting the applicance to the SBS Server via iSCSI, and then sharing the folders. It worked well, but if the server goes down for whatever reason then we lose access to these files. So I tried putting the N8800Pro into SMB mode, and sharing the folders directly from the applicance (bypassing the SBS server). However, now access t...

Sorting across columns in Excel 2003
I'm using Office 2003 and the Excel module. I have a spreadsheet with 3 columns. Columns A and C include names and phone number of people. Column B is just a blank divider. If I have the sheet alphabetized, with 10 names and numbers in column A and 10 in column B, I occasionally will go to the sheet and add someone else's name. I add it either at the bottom of column A or C. I then highlight A and C and ask to have it sorted alphbetically. Can't do it!! Only thing I've been able to do is "cut" the 10 names from column C and paste them under the 10 names in col...

In Excel
I want to effect a mass change to the sign of numeric values in a range of cells (rows and columns). put -1 in an empty cell, copy it, select the range of cells to be modified, select Paste Special and pick Multiply. MaryKaye wrote: > I want to effect a mass change to the sign of numeric values in a range of > cells (rows and columns). Put -1 in an un-used cell and copy it. Then select the cells you want to update and: Edit > PasteSpecial > multiply -- Gary''s Student - gsnu201001 "MaryKaye" wrote: > I want to effect a mass ch...

Excel 2003 Slow Start Up
I am running Excel 2003 (11 & SP2), Office Standard Edition 2003,on Windows XP. When I launch Excel the start up is very slow compared to the other MS apps, i.e., Word 2003, Outlook 2003, PowerPoint 2003, Access 2000 or Visio 2002 Professional. Why might this be? What can I do to speed up the launch? Thanks in advance. Check to see if you have extra files opening. Items selected under Tools, Add-in. Also any workbooks in your XLSTART folders. -- Jim "SysAdmMAX411" <SysAdmMAX411@discussions.microsoft.com> wrote in message news:98857B2C-9308-4CBC-B1AE-B1D10E3329...

Using exchange POP3 connector
Hello :) I've inherited a a very sick SBS 2003 client of under 10 users that I am looking to upgrade to 2008. In their current config they are using a pop3 host, hosting individual mailboxes and having exchange download the mail to each users account and using the ISP smart host to send. All OWA, RWW activity is used via the the static IP address on the router. They have expressed they wish to stay with this method of having email 'sit' on a host if anything happens to their net or email server so that it can be accessed via the hosts online web access. ...

Auto sum / total 2 fields in a record
Auto sum 2 fields in a record Hi. This is incredibly easy to do in Excel but I’m afraid it’s eluding and frustrating me in Access!!! I have a very simple table with 5 columns formatted to collect money amounts. So, the User would enter the 5 money amounts into each record. So far so good…. The first 2 fields in each record collect “Income from farm”, the second field collects “Income from shop”. I’d like a 3rd field to automatically add up the content of what’s been entered in the first 2 fields (the 3rd field should never accept user input directly) and display that resul...

Displaying/Using Unit Accounts in FRx 6.7
I have a client that wants to calculate Revenue/Expenses/Profit by number of employees. We created departmental unit accounts to accumulate the employee count numbers in Dynamics GP 10. Now we are trying to use them in an FRx Column and can't figure out how. FRx Help talks about Non-financial Book Codes but doesn't explain how to set one up. I can put a unit account on a Row, but I ca't figure out how to use that in a calculation. Thanks, Kim. Let's say you have two rows. Row 100 has a revenue account -- 4100 Row 200 has a unit account -- 9800 You would add row 300...

Output form to excel with range ????
I've read the code written by Mr Dev on the site but that is not excatly understand how the code is work.I have condition: Some data that I want to output to are on the form Header,and the others are on the detail section of my continuous form.Below is where I've got so far Dim appXL As Object Dim wkb As Object Dim wks As Object Set appXL = CreateObject("Excel.Application") Set wkb = appXL.Workbooks.Open("C:\MyFolder\My Workbook.xls") Set wks = wkb.Worksheets(1) appXL.Visible = True wks.Cells(4, 3) = [Customer] wks.Cells(6, 3) = [LotNo] wks.Cells(7, 3) = [Model] ...

Extracting text from right to left using MID and FIND
First of all I would like to thank you in advance for taking the time to look at this post and providing any help possible. I am a novice trying to learn Excel and find it to be "fun". I am trying to extract text from a string for which the character length is never the same. I have found a tutorial on how one may extract a middle name but that has confused me in since it is set up to read from left to right. What I need to do is find a lower case "h" and take all of the text to the left of the "h" up to but not including the first space encouont...

auto save
We are trying to turn auto save off, but when we close the work book, and reopen it, its turned back on again. What are we missing? Running windows 2000 operating system. Please respond to my email address. Thankyou If you install the service release mentioned in the following MSKB article, it should solve the problem: XL2000: AutoSave Settings Not Retained Between Sessions of Excel 2000 http://support.microsoft.com/default.aspx?id=231117 -- Debra Dalgleish Excel FAQ, Tips & Book List http://www.contextures.com/tiptech.html "marv" wrote: > We are trying to tur...

How do I link data from another workbook using a different file na
I'm trying to link data from a source workbook to a destination workbook using a different filename. Also the data will be save in several worksheets. I would like to keep a history of the link data for each worksheet. Which is the best way to execute this task. This formula, when placed in a cell in your current workbook, will draw the value from cell A1 of the Workbook1.xls file from the specified directory. ='C:\My Documents\[workbook1.xls]Sheet1'!A1 Vaya con Dios, Chuck, CABGx3 "Victor" <Victor@discussions.microsoft.com> wrote in message news:DD384315...

sorry, im new at excel but cant find this anywhere
Okay hey guys. The problem I'm having is with a line graph. I have two lines. I have 8 rows of data, but I have no idea how to change the numbers on the axis for both the x and the y. What it is doing is on the X-axis it has 1-8 in increments of one. This is because I have 8 rows of data. What I WOULD like is for it to show up as (each tick) is increments of 500 labeled at every 2000 increment. I hope you guys get what I'm saying. I have no idea how to change this, and I have looked for a long long time. I appreciate your help. -- cloosh -----------------------------------...

select legend pattern for individual values in an excel doughnut.
Hi, Using Excel 2003, I'm making doughnut charts. (Doughnuts, Ummm....). I want to customize the legend values. I especially want to distinguish different values with different patterns, since I can't afford to have all my reports reprinted in color. Right now, I can change the legend, but when I make a change it affects the entire doughnut ring, not the individual values. Marsh - It takes two single clicks on the bite of donut to select it, so it alone gets the new formatting. Alternatively, two single clicks to select first the legend, then the legend key (the colored square...