Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Sum function problem
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 |
#2
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Sum function problem
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 On Thu, 18 Jun 2009 12:39:01 -0700, Sue wrote: 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. |
#3
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Sum function problem
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 "Sue" wrote in message ... 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 |
#4
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Sum function problem
Thanks for your help!
-- Sue "Gord Dibben" wrote: 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 On Thu, 18 Jun 2009 12:39:01 -0700, Sue wrote: 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. |
#5
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Sum function problem
Thank you - this is the first time I've used the discussion groups and I
appreciate all the help. -- Sue "Bernard Liengme" wrote: 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 "Sue" wrote in message ... 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 |
#6
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Sum function problem
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 On Thu, 18 Jun 2009 17:08:08 -0300, "Bernard Liengme" wrote: 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 |
#7
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Sum function problem
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 -- Regards, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "Gord Dibben" <gorddibbATshawDOTca wrote in message ... 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 On Thu, 18 Jun 2009 17:08:08 -0300, "Bernard Liengme" wrote: 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 |
#8
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Sum function problem
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" <gorddibbATshawDOTca wrote in message ... 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 On Thu, 18 Jun 2009 17:08:08 -0300, "Bernard Liengme" wrote: 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 |
#9
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Sum function problem
Me too.
Thanks Gord. -- Regards Roger Govier "Bernard Liengme" wrote in message ... 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" <gorddibbATshawDOTca wrote in message ... 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 On Thu, 18 Jun 2009 17:08:08 -0300, "Bernard Liengme" wrote: 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 |
#10
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Sum function problem
You are correct RD
The sequence is crucial as you point out. SHIFT + F8 then =SUM(( clears the "ADD" function Gord On Thu, 18 Jun 2009 17:00:45 -0700, "RagDyer" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Problem with Function | Excel Worksheet Functions | |||
MAX / MIN function problem | Excel Discussion (Misc queries) | |||
Problem with function | Excel Discussion (Misc queries) | |||
Function problem | Excel Discussion (Misc queries) | |||
Problem with IF function | Excel Discussion (Misc queries) |