Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
add non-contiguent sums in one column to a Grand Total
I have a worksheet with 6 different items that are totaled approximately 140
times in separate cells in the same column. These sums for each individual item need to be put into a Grand Total on another worksheet. I have tried to write =sum( and then click on each individual cell to add them up, but I get a message saying the formula is too long. I'm thinking I need to use a filter function for this, but I can't seem to find one that works. I am very new to working with Excell and would appreciate any help I can get. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
add non-contiguent sums in one column to a Grand Total
Have you looked in the help index for SUMIF
-- Don Guillett SalesAid Software "Ryan" wrote in message ... I have a worksheet with 6 different items that are totaled approximately 140 times in separate cells in the same column. These sums for each individual item need to be put into a Grand Total on another worksheet. I have tried to write =sum( and then click on each individual cell to add them up, but I get a message saying the formula is too long. I'm thinking I need to use a filter function for this, but I can't seem to find one that works. I am very new to working with Excell and would appreciate any help I can get. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
add non-contiguent sums in one column to a Grand Total
Perhaps you could just use subtotals?
http://www.officearticles.com/excel/...soft_excel.htm ************ Hope it helps! Anne Troy www.OfficeArticles.com "Ryan" wrote in message ... I have a worksheet with 6 different items that are totaled approximately 140 times in separate cells in the same column. These sums for each individual item need to be put into a Grand Total on another worksheet. I have tried to write =sum( and then click on each individual cell to add them up, but I get a message saying the formula is too long. I'm thinking I need to use a filter function for this, but I can't seem to find one that works. I am very new to working with Excell and would appreciate any help I can get. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
add non-contiguent sums in one column to a Grand Total
You can overcome the 30 cell limit by wrapping using more parenthesis
=SUM((A1,A3,A5,A8,A11,A16,A19,A22,C17,B22,B26,D27, F25,G19,H27,C30,A28,E31,F31,H29,F9:F10,G2,I4,K4,K6 ,J8,I7,H10,J24,J26,J28,K31,I31,G34,D30,C26,F18,G10 ,I5:I6,G4,F6,J8,L7,L4,K4,A10,A12,A14,A16,A19,A22)) works however are you trying to sum in one column for values that is in another column like sum everything in B that has item 1 in A? Then you can use SUMIF =SUMIF(A:A,"Item1",B:B) You can also use a filter (datafilterautofilter), filter on the item and then use =SUBTOTAL(9,B2:B500) will only sum visible cells -- Regards, Peo Sjoblom Portland, Oregon "Ryan" wrote in message ... I have a worksheet with 6 different items that are totaled approximately 140 times in separate cells in the same column. These sums for each individual item need to be put into a Grand Total on another worksheet. I have tried to write =sum( and then click on each individual cell to add them up, but I get a message saying the formula is too long. I'm thinking I need to use a filter function for this, but I can't seem to find one that works. I am very new to working with Excell and would appreciate any help I can get. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
add non-contiguent sums in one column to a Grand Total
Another option to try would be a pivot table ..
(takes only a few clicks, and drag & drops to get us there) Assume source data is in Sheet1, cols A and B, data from row2 down, eg: Item Amt Mat1 10 Mat2 10 Mat1 10 Mat2 10 Mat5 10 Mat6 10 Mat2 10 Mat3 10 Mat1 10 etc Creating Pivot Table (steps in Excel 97): Select any cell within the source table Click Data PivotTable Report Click Next Next In step 3 of the wizard, Drag & drop Item within the ROW area Drag & drop Amt within the Data area (it'll appear as Sum of Amt) Click Finish The pivot table will appear in a new sheet to the left, yielding, for eg: Sum of Amt Item Total Mat1 30 Mat2 30 Mat3 10 Mat5 10 Mat6 10 Grand Total 90 (Unique items will be listed under "Item" with the corresponding totals next to it) -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "Ryan" wrote in message ... I have a worksheet with 6 different items that are totaled approximately 140 times in separate cells in the same column. These sums for each individual item need to be put into a Grand Total on another worksheet. I have tried to write =sum( and then click on each individual cell to add them up, but I get a message saying the formula is too long. I'm thinking I need to use a filter function for this, but I can't seem to find one that works. I am very new to working with Excell and would appreciate any help I can get. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Need to Improve Code Copying/Pasting Between Workbooks | Excel Discussion (Misc queries) | |||
Positioning Numeric Values Resulting from 6 Column Array Formula | Excel Worksheet Functions | |||
Arithmetical Mode of Criteria in Multiple Non-Adjacent columns | Excel Worksheet Functions | |||
How to group similar column titles together???? | Excel Discussion (Misc queries) | |||
Return Count for LAST NonBlank Cell in each Row | Excel Worksheet Functions |