Results 1 to 9 of 9

20050817, 01:35 #1
 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?

20050817, 02:51 #2
 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

20050817, 02:58 #3
 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

20050817, 02:59 #4
 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.

20050817, 03:01 #5
 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

20050817, 03:10 #6
 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!

20050817, 03:21 #7
 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

20050817, 03:27 #8
 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!

20050817, 23:48 #9
 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