Results 1 to 15 of 19

20050317, 17:28 #1
 Join Date
 Feb 2003
 Location
 Bend, Oregon
 Posts
 78
 Thanks
 0
 Thanked 0 Times in 0 Posts
How do I overcome SUM's 30 reference limit? (Excel 2002 SP3)
Here's a challenge for all of you:
I have a worksheet which reports over 50 indicators (rows) by reporting date (columns) where the values are collected (via links) from numerous other worksheets. I need to sum the reported values for each date (column), however, the rows are not all contiguous [e.g., =SUM(B6, B8, B10, B13:B14, B16, B18, B21, B24, B26, B29:B31, B33, B37, B39, B41, B44, B47, B49, B51, B53, B55, B57, B60, B62, B64, B68, B70, B72, B75, B77, B80, B84, B86, B88, B93, B97:B98, B100, B102, B104, B107, B109, B112, B114, B117, B121:B123, B127, B129, B132, B134, B137:B140, B143, B146, B148)] and, as you can see, create more than 30 cell and range references for each column's total.
I can't sum the entire range from B6 to B148, because several of the other cells [e.g., B15, B27, B32, etc.] contain formulas which reference the column total, and would, therefore, create a circular reference error. I could break the entire mess into several subtotal formulas which I could then sum in a grand total formula, but I can foresee this becoming a maintenance nightmare as this report grows in the future (especially considering that I will probably not be the one maintaining this worksheet).
Any suggestions would be appreciated.

20050317, 17:41 #2
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 28 Times in 28 Posts
Re: How do I overcome SUM's 30 reference limit? (Excel 2002 SP3)
You could use two sheets:
 One sheet for calculations, with the rows arranged in such a way that you can sum contiguous ranges. The cells with formulas referring to the column total are below the data rows.
 Another sheet for presentation purposes, with cells linked to the calculation sheet, and with the rows arranged in the way you need for reporting.

20050317, 18:28 #3
 Join Date
 Feb 2003
 Location
 Bend, Oregon
 Posts
 78
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: How do I overcome SUM's 30 reference limit? (Excel 2002 SP3)
That might solve several issues, all at once. And make the whole report easier to maintain for everybody.
... Sometimes I can be completely blind to the obvious (and simple) solutions. It's good to have clearer minds to consult.
Thanks Hans!

20050317, 19:21 #4
 Join Date
 Jan 2001
 Location
 South Carolina, USA
 Posts
 7,295
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: How do I overcome SUM's 30 reference limit? (Excel 2002 SP3)
You could just split it into two SUMs and add them together, something like this: =SUM(B6, B8, B10, B13:B14, B16, B18, B21, B24, B26, B29:B31, B33, B37, B39, B41, B44, B47, B49, B51, B53, B55, B57, B60, B62, B64, B68, B70, B72, B75)+SUM( B77, B80, B84, B86, B88, B93, B97:B98, B100, B102, B104, B107, B109, B112, B114, B117, B121:B123, B127, B129, B132, B134, B137:B140, B143, B146, B148).
Legare Coleman

20050317, 19:55 #5
 Join Date
 Feb 2001
 Location
 Weert, Limburg, Netherlands
 Posts
 4,812
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: How do I overcome SUM's 30 reference limit? (Excel 2002 SP3)
Just for illustration's sake. It is little known that you can nest arguments in the SUM function:
=SUM((B6, B8, B10, B13:B14, B16, B18, B21, B24, B26, B29:B31),( B33, B37, B39, B41, B44, B47, B49, B51, B53, B55, B57, B60, B62, B64, B68, B70, B72, B75), (B77, B80, B84, B86, B88, B93, B97:B98, B100, B102, B104, B107),( B109, B112, B114, B117, B121:B123, B127, B129, B132, B134, B137:B140, B143, B146, B148))
This one has only four arguments...Jan Karel Pieterse
Microsoft Excel MVP, WMVP
www.jkpads.com
Professional Office Developers Association

20050317, 23:55 #6
 Join Date
 Feb 2003
 Location
 Bend, Oregon
 Posts
 78
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: How do I overcome SUM's 30 reference limit? (Excel 2002 SP3)
Jan Karel,
This is very powerful. Do you know if there are any other functions where nesting of arguments is undocumented?

20050318, 03:04 #7
 Join Date
 Mar 2005
 Location
 Wellington, Wellington
 Posts
 11
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: How do I overcome SUM's 30 reference limit? (Excel 2002 SP3)
Could be even simpler  would a SUMIF function work  bearing in mind you can have multiple conditions.
Gravey

20050318, 08:36 #8
 Join Date
 Feb 2001
 Location
 Weert, Limburg, Netherlands
 Posts
 4,812
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: How do I overcome SUM's 30 reference limit? (Excel 2002 SP3)
Well, of course the related ones: stdev, min, max, average, .... Others, well, you'd have to test I guess <g>.
Jan Karel Pieterse
Microsoft Excel MVP, WMVP
www.jkpads.com
Professional Office Developers Association

20050318, 12:49 #9
 Join Date
 Sep 2001
 Location
 Birmingham, Alabama, USA
 Posts
 40
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: How do I overcome SUM's 30 reference limit? (Excel 2002 SP3)
A question from someone who know far less them most of the people responding to questions in the lounge:
If I were doing this I would simply use +B6+B8+B10+B13+B14+

20050318, 12:54 #10
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 28 Times in 28 Posts
Re: How do I overcome SUM's 30 reference limit? (Excel 2002 SP3)
One possible reason is that SUM will ignore nonnumeric values, while + will return an error value if there are nonnumeric values among the arguments.
<table border=1><td></td><td align=center>A</td><td align=center>B</td><td align=center>C</td><td align=center>1</td><td align=right>1</td><td>=SUM(A1:A3)</td><td align=right>4</td><td align=center>2</td><td>two</td><td>=A1+A2+A3</td><td>#VALUE!</td><tr><td align=center>3</td><td align=right>3</td><td align=right>

20050318, 12:57 #11
 Join Date
 Feb 2004
 Location
 Cape Town, RSA
 Posts
 3,444
 Thanks
 0
 Thanked 1 Time in 1 Post
Re: How do I overcome SUM's 30 reference limit? (Excel 2002 SP3)
Also, functions will update their references if the structure of the worksheet changes, where formulas need to be maintained manually if the structure changes!
Regards,
Rudi

20050318, 13:22 #12
 Join Date
 Sep 2001
 Location
 Birmingham, Alabama, USA
 Posts
 40
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: How do I overcome SUM's 30 reference limit? (Excel 2002 SP3)
Thanks for the reply.
I understand the response from Hans and it is a good point.
But Rudi I am not sure what you are referring to when you say that formulas need to be maintained manually if the structure changes. If I add rows or columns or move the cells the formulas are updated. I understand that if I inert a row or column into the middle of a range referenced by the sum function it is included in the total which it would not be if I added up the individual cells.
What is it that I am missing?
M

20050318, 13:31 #13
 Join Date
 Feb 2004
 Location
 Cape Town, RSA
 Posts
 3,444
 Thanks
 0
 Thanked 1 Time in 1 Post
Re: How do I overcome SUM's 30 reference limit? (Excel 2002 SP3)
Sorry if I was unclear!
<hr>I understand that if I inert a row or column into the middle of a range referenced by the sum function it is included in the total which it would not be if I added up the individual cells.<hr>Exactly as you say! The formula will not be updated, but the function will include the newly inserted row/column by adjsting its references. Also, if you insert a row 11 into a sheet with a formula =sum(A1:A10), the references will change to =sum(A1:A11). (Does not just have to be in the middle of the range!)
Hope I am clearer!Regards,
Rudi

20050318, 13:32 #14
 Join Date
 Sep 2001
 Location
 Birmingham, Alabama, USA
 Posts
 40
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: How do I overcome SUM's 30 reference limit? (Excel 2002 SP3)
Thanks Much
M

20050318, 16:22 #15
 Join Date
 Feb 2003
 Location
 Bend, Oregon
 Posts
 78
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: How do I overcome SUM's 30 reference limit? (Excel 2002 SP3)
Gravey,
I didn't quite understand what you were suggesting by using a SUMIF function instead of a SUM function. SUMIF has the same issue as SUM, in that using a single range (i.e., B6:B148) would include reference to the cells which would set up the circular reference error. And I've never found a way to include more than a single range (or sumrange) in a SUMIF. <font color=blue>If anyone is aware of a way to use multiple ranges, please let me know. It would be very useful for some of my more "hair brained" calculations.</font color=blue>