Sum function problem

[Sue]

We're trying to get the sum of about 60 cells [not in a range, unfortunately]
on an enormous spreadsheet. As the cells references are entered into the
formula the cells are outlined in color and the cell reference in the formula
turns a corresponding color. All good. After about 30 entries, the cells
are no longer being highlighted and the cell references are in black. After
entering all of the cell references in the formula and closing with the
parenthesis, we hit enter and get an error message. I can't tell you the
exact wording since it's my boss's spreadsheet and she decided to work it out
with subtotals and totals of subtotals. But there must be a reason and I'd
like to know what it is - and how to fix it. Thanks.

 

[Sue]

Thanks for your help!
--
Sue

Most functions.....SUM included.........have a limit of 30 arguments.

60 non-contiguous cells surpass that limit by twice.

One workaround is to add extra parens.

=SUM((A1,B5,F7,etc))

Note the double parens at each end of formula.


Gord Dibben MS Excel MVP

We're trying to get the sum of about 60 cells [not in a range, unfortunately]
on an enormous spreadsheet. As the cells references are entered into the
formula the cells are outlined in color and the cell reference in the formula
turns a corresponding color. All good. After about 30 entries, the cells
are no longer being highlighted and the cell references are in black. After
entering all of the cell references in the formula and closing with the
parenthesis, we hit enter and get an error message. I can't tell you the
exact wording since it's my boss's spreadsheet and she decided to work it out
with subtotals and totals of subtotals. But there must be a reason and I'd
like to know what it is - and how to fix it. Thanks.

 

[Sue]

Thank you - this is the first time I've used the discussion groups and I
appreciate all the help.
--
Sue

Gord has given a neat solution. Another is
=SUM(cell1, cell2,.....celln)+SUM(celln=1, cellN+2)
But since there are not in a range why not use a simple formula similar to:
=A1+C3+D5+F5+G6+H7........
best wishes
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

We're trying to get the sum of about 60 cells [not in a range,
unfortunately]
on an enormous spreadsheet. As the cells references are entered into the
formula the cells are outlined in color and the cell reference in the
formula
turns a corresponding color. All good. After about 30 entries, the cells
are no longer being highlighted and the cell references are in black.
After
entering all of the cell references in the formula and closing with the
parenthesis, we hit enter and get an error message. I can't tell you the
exact wording since it's my boss's spreadsheet and she decided to work it
out
with subtotals and totals of subtotals. But there must be a reason and
I'd
like to know what it is - and how to fix it. Thanks.

 

[Gord Dibben]

That's too hard Bernard........entering all those "+" signs and clicking
cells<g>

Hit SHIFT + F8 then =SUM(( and click your way around. Excel adds the
commas.


Gord

 

[RagDyer]

That works for me Gord *only* if I enter:
=sum(
*FIRST* ... THEN do <Shift> <F8>

Nice trick though!<bg>
Learn something new every day ... right?<g>

 

[Bernard Liengme]

That a new one on me - maybe my worksheets are too tidy <g>
best wishes

 

[Roger Govier]

Me too.
Thanks Gord.

--
Regards
Roger Govier

That a new one on me - maybe my worksheets are too tidy <g>
best wishes
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

 

[Gord Dibben]

You are correct RD

The sequence is crucial as you point out.

SHIFT + F8 then =SUM(( clears the "ADD" function


Gord