Applying formulas only to the subtotals of a data list

I have a large data list with subtotals (in fact with nested subtotals). I 
have collapsed the list so that only the subtotals are showing. Next I set up 
a few columns to the right of the data list with the intent of applying 
certain calculations, again ONLY to the subtotals.

Here lieth the problem: when I set up the formulas for the first subtotal 
and attempt to simply drag it down, Excel obviously assumes that the copied 
formula applies to the next row of the full-blown, uncollapsed  data list. Is 
there any way to accomplish the task without having to manually create a 
separate table that only displays the subtotaled data? That would be a very 
big (and boring) job. Incidentally the row interval between subtotals varies 
widely.
0
KG (96)
9/10/2005 5:00:02 PM
excel.misc 78881 articles. 5 followers. Follow

5 Replies
638 Views

Similar Articles

[PageSpeed] 16

If the number of subtotals is small, maybe you could just do the formulas for
one row (multiple cells worth of formulas???).

Then copy those cells
select the next visible row (next subtotal line) and ctrl-v

down arrow
ctrl-v

down arrow
ctrl-v 

(and so forth)

====
Or modify your formulas....

Look for a key (Like the word Total in the key column)

I'd show all the rows and use a formula like:
=IF(ISERROR(SEARCH("total",A2)),NA(),"yourformulahere")
and drag down

Then select that column
edit|goto|special
check Formulas
and keep errors checked, but uncheck Numbers, Text, Logicals.
Then hit the delete key to clear contents of those error cells.

========
On the other hand, you may want to play around with pivottables.

After you create the pivottable, maybe using some of the techniques at Debra
Dalgleish's site would come in handy:

http://www.contextures.com/xlPivot10.html



KG wrote:
> 
> I have a large data list with subtotals (in fact with nested subtotals). I
> have collapsed the list so that only the subtotals are showing. Next I set up
> a few columns to the right of the data list with the intent of applying
> certain calculations, again ONLY to the subtotals.
> 
> Here lieth the problem: when I set up the formulas for the first subtotal
> and attempt to simply drag it down, Excel obviously assumes that the copied
> formula applies to the next row of the full-blown, uncollapsed  data list. Is
> there any way to accomplish the task without having to manually create a
> separate table that only displays the subtotaled data? That would be a very
> big (and boring) job. Incidentally the row interval between subtotals varies
> widely.

-- 

Dave Peterson
0
petersod (12004)
9/10/2005 5:31:59 PM
the problem I'm having is that: 

1) One of the formulas I am using is counting the total number of items that 
make up the subtotal and 
2) the number of items that go into the subtotal varies widely; for example, 
one subtotal may be from a list of 5 items whereas the next may be from a 
list of 45.

I don't see how I can copy and paste a formula that computes a range of 5 
items to the next subtotal that is composed of a much larger number of items.

"KG" wrote:

> I have a large data list with subtotals (in fact with nested subtotals). I 
> have collapsed the list so that only the subtotals are showing. Next I set up 
> a few columns to the right of the data list with the intent of applying 
> certain calculations, again ONLY to the subtotals.
> 
> Here lieth the problem: when I set up the formulas for the first subtotal 
> and attempt to simply drag it down, Excel obviously assumes that the copied 
> formula applies to the next row of the full-blown, uncollapsed  data list. Is 
> there any way to accomplish the task without having to manually create a 
> separate table that only displays the subtotaled data? That would be a very 
> big (and boring) job. Incidentally the row interval between subtotals varies 
> widely.
0
KG (96)
9/10/2005 6:19:02 PM
Can you post a small sample of your data, your expected results, and 
your formula?

In article <FB2099D4-9017-4496-B364-333EE8691AD9@microsoft.com>,
 "KG" <KG@discussions.microsoft.com> wrote:

> the problem I'm having is that: 
> 
> 1) One of the formulas I am using is counting the total number of items that 
> make up the subtotal and 
> 2) the number of items that go into the subtotal varies widely; for example, 
> one subtotal may be from a list of 5 items whereas the next may be from a 
> list of 45.
> 
> I don't see how I can copy and paste a formula that computes a range of 5 
> items to the next subtotal that is composed of a much larger number of items.
0
domenic22 (716)
9/10/2005 7:40:22 PM
I have that the f ollowing sample illustrates my problem:


NAME  PRODUCT          SIZE   QUANTITY 
Smith	ABC	Medium	2,500
Smith	ABC	Large	4,500
Subtotal    ABC		7,000
SMITH  GRAND TOTAL	7,000
			
Jones	CBC	Medium	1,000
Subtotal CBC		1,000
Jones	DECK	Small	1,000
Subtotal DECK		1,000
Jones	XPK	Medium	1,000
Jones	XPK	Large	2,000
Subtotal XPK		3,000
JONES GRAND TOTAL		5,000

Visualize that I hide all rows, except the subtotals and the customer grand 
totals and that I want to analyze the customer grand totals.The specific goal 
is to count the number of product subtotals adding up to the custoomer grand 
total, using a simple COUNTA formula. My problem is that I cannot drag the 
formula for SMITH GRAND TOTAL to the JONES GRAND TOTAL, because one has a 
range of one subtotal whereas the other has a range of three subtotals.

I hope that explains the issue.

"KG" wrote:

> I have a large data list with subtotals (in fact with nested subtotals). I 
> have collapsed the list so that only the subtotals are showing. Next I set up 
> a few columns to the right of the data list with the intent of applying 
> certain calculations, again ONLY to the subtotals.
> 
> Here lieth the problem: when I set up the formulas for the first subtotal 
> and attempt to simply drag it down, Excel obviously assumes that the copied 
> formula applies to the next row of the full-blown, uncollapsed  data list. Is 
> there any way to accomplish the task without having to manually create a 
> separate table that only displays the subtotaled data? That would be a very 
> big (and boring) job. Incidentally the row interval between subtotals varies 
> widely.
0
KG (96)
9/10/2005 9:56:02 PM
Why not add a subtotal using countA for the Product column?

When you apply data|subtotals, you can still use Sum as your function, but
include the product column.

Since you're summing, all your:
=subtotal(9,Bx:By)
will be 0's (assuming that all your product codes are text).

So select column B and then
edit|replace
what: (9,
with: (3,
replace all

Then you can use that subtotal in your formula.

I don't know if this will work, since you haven't shared the formula.

KG wrote:
> 
> the problem I'm having is that:
> 
> 1) One of the formulas I am using is counting the total number of items that
> make up the subtotal and
> 2) the number of items that go into the subtotal varies widely; for example,
> one subtotal may be from a list of 5 items whereas the next may be from a
> list of 45.
> 
> I don't see how I can copy and paste a formula that computes a range of 5
> items to the next subtotal that is composed of a much larger number of items.
> 
> "KG" wrote:
> 
> > I have a large data list with subtotals (in fact with nested subtotals). I
> > have collapsed the list so that only the subtotals are showing. Next I set up
> > a few columns to the right of the data list with the intent of applying
> > certain calculations, again ONLY to the subtotals.
> >
> > Here lieth the problem: when I set up the formulas for the first subtotal
> > and attempt to simply drag it down, Excel obviously assumes that the copied
> > formula applies to the next row of the full-blown, uncollapsed  data list. Is
> > there any way to accomplish the task without having to manually create a
> > separate table that only displays the subtotaled data? That would be a very
> > big (and boring) job. Incidentally the row interval between subtotals varies
> > widely.

-- 

Dave Peterson
0
petersod (12004)
9/10/2005 10:16:09 PM
Reply:

Similar Artilces:

Invisible data in combo box
Hi there, I have recently deployed a database. My client is having issues in that a combo box has invisible data (the data is truly there, he can select an item in the middle of the drop down and other bound data populates, but it does not display). I have been able to isolate the issue to being a problem on his end; as I've opened it on 4 different instances of Access (3 X 2003, 1 X 2007) and cannot replicate the issue. I've also shipped him a version with different colors of font for its text, and this has not worked, either. He is using this database on 2 separate machine...

find column with text data in row 1
I have the following macro which bounces me back and forth between b:k and m:u Private Sub Workbook_SheetBeforeDoubleClick( _ ByVal Sh As Object, ByVal target As Range, Cancel As Boolean) If Not Intersect(target, Range("B:J")) Is Nothing Then target.Offset(0, 11).Select If Not Intersect(target, Range("M:U")) Is Nothing Then target.Offset(0, -11).Select End Sub Now I would like to build on that. My K Column is my delimiter with the static text 'Totals'. I would like to be able to have the functionality of the above without have the number of columns static, so...

Formula 'GOTO'
Hello, I have a user who prior to a reimage of her drive had the ability to place her cursor in a cell in Excel(workbook 1) and if the formula was linked from another worksheet(workbook 2) she could double click and it would take her to the original formula (workbook 2). Of course now that we have re-imaged her drive she no longer has the ability to do this. She is running OS XP and Office 2002, sp2. Tools|edit tab Uncheck "edit directly in cell" and try it out. helpdesk genie wrote: > > Hello, > I have a user who prior to a reimage of her drive had the ability to place h...

Storing Data
If I want to perform a calculation I am using a control source of a field. the problem is I want to store that date in the table and it won't let me. How do I store the calculated field in the table. My formula works here it is. =DateAdd("m",[ServiceInterval],[tblLog Subform].[Form]![ServiceDate]) I want to store this date in the NextServiceDate field in my service table. Calvin A scan of the tablesdbdesign newsgroup will reveal that it is rarely necessary to store a calculated value, particularly so if you can calculate it "one the fly" in a query. That isn't...

How do I zip sort mailing list when I have 5 digit AND 9 digit zip
I have a mailing list with zips in XXXXX and XXXXX-XXXX all mixed in. When I sort them, it seems to put all the 5 digits on the top half and 9 digits on the bottom half. I have made sure the column settings are set to special/zipcode. I have also tried setting them as Zip Code + 4 but it converts all the XXXXX versions to 0000X-XXXX. What am I doing wrong? Convert to TEXT -- Don Guillett Microsoft MVP Excel SalesAid Software dguillett@gmail.com "Greg" <Greg@discussions.microsoft.com> wrote in message news:97AB96D9-EAE6-4E6F-8AA1-1DE4137EDA06@microsoft.c...

in list display only yes
I have a report that is a list of all employees. I have a field for "medicalNeeds". It is a yes or no entry in the table. I'm listing all employees in the report but only want the yes to display for employees that have medical needs..if the entry is now, I don't want it to display. How to I set the properties to only show yes but still show all the records for all the employees. Thank you for your help. On Fri, 29 Feb 2008 09:10:01 -0800, PowellGirlTN wrote: > I have a report that is a list of all employees. I have a field for > "medicalNeeds". ...

Excel/Visio Form for entering data
I've seen this done before, just trying to figure out how to accomplish it. I have an Excel spreadsheet containing a Visio network diagram with text fields for entering data (IP addresses, etc). I'm trying to recreate this form with a new diagram. Once I've pasted the Visio diagram into Excel, I'm having difficulty getting Excel to allow me to specify fields and enter data. There has to be an easy way to do this. Anyone have a good resource or know how to do it? ...

Data Files
If I split up my one data file in outlook into two, does it make outlook run faster? what if i put all my archive in one .pst file and then removed from outlook? i would only add it to outlook once a month to clean up and archive things. would that make outlook run faster? the real question i have is does it matter whether i temporarily remove the archive .pst file from outlook to make it run faster? thanks jim A smaller .pst will allow Outlook to run faster. If you empty your deleted items folder and then archive olds messages, it creates an archive.pst file. Remember, you can open any ...

MS Excel 2003 cannot auto calculate formula, need to press F9 each time
hi, I don't know why my excel 2003 new worksheet cannot auto calulate formula (eg. summation), i need to press F9 and it will refresh and show the new figure. there is "calculate" word at the left hand bottom of the screen. what is the likely reason ? it was running fine 2 weeks ago. any advise is greatly appreciated. rgds. Tools>Options>Calculation tab, check Automatic -- Kind regards, Niek Otten Microsoft MVP - Excel <sg_s123@yahoo.com.sg> wrote in message news:d5393a73-eb7d-4e08-8fab-5f4ab895f77a@e23g2000prf.googlegroups.com... | hi, | | I don't know w...

Locking and Protecting and NOT displaying formulas
Is it possible to not only lock but also NOT display some formulas we have in a ROI-Return On Investment-spreadsheet??? Sum of the formulas are proprietary and would like them to not even be displayed within the spreadsheet that will be distributed to prospects leads. Let me know. Thanks! RTP, format, cells, protection, check hidden for the cells with formulas, protect the sheet. be aware that this protection is VERY weak and can be broken easily. -- Paul B Always backup your data before trying something new Please post any response to the newsgroups so others can benefit from it Feedb...

using excel data for mail merge with Word
When I try to use an excel worksheet as my data to create address labels with Word, the text is unreadable. Seems very basic - but what am I doing wrong? Adriannae, have a look here for some tips http://www.mvps.org/dmcritchie/excel/mailmerg.htm -- Paul B Always backup your data before trying something new Please post any response to the newsgroups so others can benefit from it Feedback on answers is always appreciated! Using Excel 2000 & 97 ** remove news from my email address to reply by email ** "adrianne" <anonymous@discussions.microsoft.com> wrote in message news:...

keep fill color when copying down a formula?
Hello. I've got a spreadsheet where every other row is filled grey or white. This makes things a bit easier to read in print, but the problem is, when I copy a formula down a column, the fill color comes along with the formula. Is there any way I can ``lock'' the fill color to the cells so that this doesn't happen? Thanks for any help that's out there -- I really appreciate it. R go to the edit menu, choose paste special then check formulas and number formats ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~...

Need formula to cal percentage of 2 numbers
Hi, I would like the formula that will compare two numbers and give me the percentage in the third column. ex C4 compared to B4 put in cloumn D4 as a percentage, New to excel and need to know how to put the formula in the cell too? thanks for hel p in advance -- Mike B =C4/B4 and format as percentage -- HTH Bob Phillips "mike b" <mikeb@discussions.microsoft.com> wrote in message news:5D45EB6A-F7D6-4B53-B3DC-EEA724E69298@microsoft.com... > Hi, > > I would like the formula that will compare two numbers and give me the > percentage in the third column. > ...

Sending Current Data record in Form
I have created a form which I want to be filled in and send via email. I have uncovered the email commands but I am having issues in that when I make the call to fill the form, all my records are being filled-in WHEN I only want the current one to be generated. How can I do this? ...

Using Excel 2000. All of a sudden none of my formulas wiil work.
i have tried to get help in excel help and it says more information is available on the web, I can't find it. I ran disk cleanup and disk defrag over the weekend. I am running a new computer and transferred my MS Office 2000 to it about 4 months ago and has been working fine until this week. Any help will be greatly appreciated. Thanks in advance Tammy thch91 All of a sudden none of my formulas wiil work. Thanks Tammy "Tammy_91" wrote: > i have tried to get help in excel help and it says more information is > available on the web, I can't find it. I ran...

using row number or column letters in formula
Now I'm trying to use a row number in a formula so I don't have to type the same formula individually on each row 12 times! My formula is currently this =SUM(INDIRECT("'"&'Control Sheet'!$F$57&"'!G7")+INDIRECT("'"&'Control Sheet'!$F$58&"'!G7")+INDIRECT("'"&'Control Sheet'!$F$59&"'!G7")+INDIRECT("'"&'Control Sheet'!$F$60&"'!G7")+INDIRECT("'"&'Control Sheet'!$F$61&"...

retain formula in additional workbooks
And the question is? -- Bernard V Liengme www.stfx.ca/people/bliengme remove caps from email "Lynch" <Lynch@discussions.microsoft.com> wrote in message news:818D246B-FBAE-4C0E-B5F2-FA2B00158772@microsoft.com... > ...

Excel
We're using Outlook 2003 / Excel. The refresh data option (DATA / Refresh Data) use to work fine on my Excel documents. All of a sudden the field is now greyed out, and thus can't be selected... Anyone know why, or how to correct this? ....ok, I've figured it out. If a cell with numbers/figures is NOT selected, then the Refresh Data button will be greyed out. Once the cell is selected, all is ok! "Ivan T. Williams" <itw@discussions.microsoft.com> wrote in message news:%23C2EDJlLGHA.2696@TK2MSFTNGP14.phx.gbl... > We're using Outlook 2003 / Excel. Th...

Dragging data onto a chart
I used to (in Office2003) be able to take a column of data and drag it onto a graph on a page to create an extra line or sctater-line plot. With the graph now beign a separate object, it this functionalilty only available through copy & paste? Hi, Drag+drop data has be deprecated in 2007. Cheers Andy user dan wrote: > I used to (in Office2003) be able to take a column of data and drag it onto a > graph on a page to create an extra line or sctater-line plot. With the graph > now beign a separate object, it this functionalilty only available through > copy & paste? ...

Loading Data into Multiple sheets in 1 book
Hi All I have 2 questions 1. I am trying to automatically create a workbook with a number of sheets in it with different data. I see how the command mode allows you to feed data to a (one/first) sheet (C:\Program Files\Microsoft Office\Office\excel.exe" H:\':DETAIL.XFER.SHORT.FILENAME) but how do you get data to sheets 2 3 4 etc in the workbook? 2. Can you format the data going into the work sheet such that some of the data would have bold text? Thanks in advance Ralph ...

Excel: showing/printing formulas
Hi! Where can I change the cells to show the formulas (rather than the result of the equation) Thx. RG, You can go to the Tools menu, Options, then the View tab. There, check the "Formulas" option. Or you can use the keyboard shortcut, CTRL+` -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "RG" <anonymous@discussions.microsoft.com> wrote in message news:BEC56831-61DB-459E-9124-555B9C4898C0@microsoft.com... > Hi! Where can I change the cells to show the formulas (rather than the result of the equation) Thx....

can't apply filter because all records are locked
Hi - I have searched the forums for similar problems, but have not found a solution for this. I created a database with the data tables included in the database file. After completing development of the database, I split it into a front end and a back end, creating links from the front end to the the data tables on the back end. This seems to work well... The only issue i am having is that, on the main form, whenever i use the built in search functionality (a 2nd form applies a filter to the Main form), Access displays the following prompt: "can' apply the filter because all the recor...

data permutation / combination
Hi i need to find a way to permute my data in an excel sheet. I have 5 different group of data which i need to permute with. for example rate diame material surface schedule 150 1/2 321 rf 10 300 3/4 304 ff 40 600 1 316 rj xs 900 1-1/4 105 80 1500 1-1/2 alloy 20 160 2500 2 ens s32720 xxs 2-1/2 3 3-1/2 4 4-1/2 5 5-1/2 6 well for diame 1, such as for...

Finding Formula?
I need to create a formula that will set a value from a table. For example, I have these data on Sheet 1: ID Name 01 Mark Anthony 02 Test Sample 03 Example Test 04 Beta VHS On Sheet 2, I have the same table but I need to type only the name on one column and the ID is automatically indicated on the same row. I have tried the Lookup function but I can't make this run. Please help. Mark Vlookup wouldn't work in this situation since the actual lookup column is not on the left. Lookup would work, but that can't be configured to *insure* an exact ...

CF for Row when CF is applied to Column
I've read through the postings but can't find an answer to my issue. Seems simple enough but I can't figure it out. I have a spreadsheet which I've applied conditional formatting to a column so that if any text appears in a cell, to color that cell yellow. What I'd like is for the row from A:O be colored yellow, not just the J cell. I'm using 07. Could someone please explain the steps in English to me or point me to the appropriate post? Cuz, I ain't figrin it out! Thanks for the help in advance! --Dax -- I would give my left hand to ...