Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.newusers
Sue Sue is offline
external usenet poster
 
Posts: 285
Default 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   Report Post  
Posted to microsoft.public.excel.newusers
external usenet poster
 
Posts: 22,906
Default 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   Report Post  
Posted to microsoft.public.excel.newusers
external usenet poster
 
Posts: 1,104
Default 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   Report Post  
Posted to microsoft.public.excel.newusers
Sue Sue is offline
external usenet poster
 
Posts: 285
Default 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   Report Post  
Posted to microsoft.public.excel.newusers
Sue Sue is offline
external usenet poster
 
Posts: 285
Default 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   Report Post  
Posted to microsoft.public.excel.newusers
external usenet poster
 
Posts: 22,906
Default 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   Report Post  
Posted to microsoft.public.excel.newusers
external usenet poster
 
Posts: 3,572
Default 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   Report Post  
Posted to microsoft.public.excel.newusers
external usenet poster
 
Posts: 1,104
Default 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   Report Post  
Posted to microsoft.public.excel.newusers
external usenet poster
 
Posts: 2,480
Default 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   Report Post  
Posted to microsoft.public.excel.newusers
external usenet poster
 
Posts: 22,906
Default 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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Problem with Function sgreene Excel Worksheet Functions 12 April 17th 08 11:19 PM
MAX / MIN function problem [email protected] Excel Discussion (Misc queries) 4 April 13th 08 02:20 AM
Problem with function Mike Excel Discussion (Misc queries) 8 June 19th 07 02:53 PM
Function problem Winnie Excel Discussion (Misc queries) 2 October 9th 06 01:31 PM
Problem with IF function [email protected] Excel Discussion (Misc queries) 5 January 19th 06 05:11 PM


All times are GMT +1. The time now is 03:36 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"