# Thread: How do I overcome SUM's 30 reference limit? (Excel 2002 SP3)

1. ## 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.

2. ## 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.

3. ## 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!

4. ## 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).

5. ## 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...

6. ## 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?

7. ## 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

8. ## 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>.

9. ## 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+

10. ## Re: How do I overcome SUM's 30 reference limit? (Excel 2002 SP3)

One possible reason is that SUM will ignore non-numeric values, while + will return an error value if there are non-numeric 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>

11. ## 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!

12. ## Re: How do I overcome SUM's 30 reference limit? (Excel 2002 SP3)

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

13. ## 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!

Thanks Much

M

15. ## 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 sum-range) 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>

Page 1 of 2 12 Last

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•