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.

Have you looked in the help index for SUMIF

--
Don Guillett
SalesAid Software
dguillett1@austin.rr.com
"Ryan" <Ryan@discussions.microsoft.com> wrote in message
news:C9977F47-5704-438C-BD3C-5FF1064491A0@microsoft.com...
>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.

Perhaps you could just use subtotals?
http://www.officearticles.com/excel/...soft_excel.htm
************
Hope it helps!
Anne Troy
www.OfficeArticles.com

"Ryan" <Ryan@discussions.microsoft.com> wrote in message
news:C9977F47-5704-438C-BD3C-5FF1064491A0@microsoft.com...
>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.

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 (data>filter>autofilter), filter on the item and
then use

=SUBTOTAL(9,B2:B500)

will only sum visible cells

--
Regards,

Peo Sjoblom

Portland, Oregon




"Ryan" <Ryan@discussions.microsoft.com> wrote in message
news:C9977F47-5704-438C-BD3C-5FF1064491A0@microsoft.com...
>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.

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" <Ryan@discussions.microsoft.com> wrote in message
news:C9977F47-5704-438C-BD3C-5FF1064491A0@microsoft.com...
> 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.