Page 1 of 2 12 LastLast
Results 1 to 15 of 19
  1. #1
    Star Lounger
    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.

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

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

  4. #4
    Uranium Lounger
    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

  5. #5
    Platinum Lounger
    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.jkp-ads.com
    Professional Office Developers Association

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

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

  8. #8
    Platinum Lounger
    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.jkp-ads.com
    Professional Office Developers Association

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

  10. #10
    Plutonium Lounger
    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 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. #11
    Gold Lounger
    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

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

  13. #13
    Gold Lounger
    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

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

  15. #15
    Star Lounger
    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 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 LastLast

Posting Permissions

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