Results 1 to 9 of 9
  1. #1
    2 Star Lounger
    Join Date
    Apr 2004
    Location
    Jacksonville, Florida, USA
    Posts
    117
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Summing calculations (Excel 2000)

    I have a row of calculated results, one per column, which I wish to add. I want the sum of B35:J35 where each of those cells is produced from a formula. If one of the cells is blank I get an #N/A which is not the result I want. The result I want is the sum of the cells that have results. How do I do this?

  2. #2
    Silver Lounger
    Join Date
    Jul 2001
    Location
    Ottawa, Ontario, Canada
    Posts
    1,609
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Summing calculations (Excel 2000)

    The formula =SUM(B35:J35) should work.
    Regards
    Don

  3. #3
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Summing calculations (Excel 2000)

    Lets say that B35 contains a formula like:

    <code>
    =xyz(A1)
    </code>

    Then you could change that formula to something like this:

    <code>
    =IF(ISERROR(xyz(A1)),"",xyz(A1))
    </code>

    Then if the xyz(A1) results in an error, then the error value in the cell will be replaced with a null string. If you do that for all of the cells B35:J35, then =SUM(B35:J35) will sum all of the cells in the range that contain numeric values.
    Legare Coleman

  4. #4
    2 Star Lounger
    Join Date
    Apr 2004
    Location
    Jacksonville, Florida, USA
    Posts
    117
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Summing calculations (Excel 2000)

    That's what I thought but it doesn't work if any of the cells aren't showing a result of the formula that's in them.

    All of the row 35 cells (columns B:J) have the formula =(B31+B33)-B8 in them, but since each column is used for a different year, not all of the columns are in use at any one time. Therefore when I try to get a sum of the ones that are in use, I get an #N/A message. I'm trying to figure out how to get a sum of only the columns that show a number in row 35.

  5. #5
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Summing calculations (Excel 2000)

    The SUM function will ignore strings in the cells, but it does not ignore error values like #N/A. If any of the cells in the range contain an error value, then the result of the SUM function will be that error value.
    Legare Coleman

  6. #6
    2 Star Lounger
    Join Date
    Apr 2004
    Location
    Jacksonville, Florida, USA
    Posts
    117
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Summing calculations (Excel 2000)

    Bingo! I had conditional formatting in those cells that did the same thing, but this is a much cleaner way to do it. AND, this lets me add the cells together, where the conditional formatting just gave me another error message. Thanks much!

  7. #7
    Silver Lounger
    Join Date
    Jul 2001
    Location
    Ottawa, Ontario, Canada
    Posts
    1,609
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Summing calculations (Excel 2000)

    When a given year is not in use, what is contained in rows 8, 31, 33, and 35 of that column?
    Regards
    Don

  8. #8
    2 Star Lounger
    Join Date
    Apr 2004
    Location
    Jacksonville, Florida, USA
    Posts
    117
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Summing calculations (Excel 2000)

    They all have formulas, but I had conditional formatting on all of them to supress the error messages. I took Legare's advice, removed the conditional formatting in row 35 and redid it with an if statement instead, so now the sum function in b36 picks up the columns that are in use and ignores the others. Thanks much for your help!

  9. #9
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Summing calculations (Excel 2000)

    A clarification (which is the root of the intial problem). Conditional formatting does not "supress the error messages" it only changes the formatting and the display. The error messages are still there, you just don't see them.

    Steve

Posting Permissions

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