# Thread: Summing calculations (Excel 2000)

1. ## 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. ## Re: Summing calculations (Excel 2000)

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

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

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

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

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