zero denominator

[Amanda]

I have a problem to add numbers with cells that has error. Any advice what
can be done? Thanks

A B C
a 5 5 5
b 1 1 0
a/b 5 5 #DIV/0! SUM A+B+C #DIV/0!

 

[Teethless mama]

=SUMIF(A1:C1,"<1E100")

 

[JoeU2004]

I have a problem to add numbers with cells that has error.
Any advice what can be done?

First, avoid the #DIV/0 error. Use one of the following formulas, based on
your preference:

=if(C2=0,"",C1/C2)

=if(C2=0,0,C1/C2)

I am assuming that the rows labeled "a" and "b" are rows 1 and 2.

Second, if you choose the first formula, compute the sum with SUM(A3,B3,C3)
instead A3+B3+C3, which would result in a #VALUE error because of the ""
text.

PS: In the future, it would be easier if you posted examples with actual
Excel row and column references instead abstract names like "a", "b" and
"a/b". "SUM A+B+C" is particular misleading.


----- original message -----

 

[Amanda]

Hi Joe & teethless mama,
Thanks. This helps. Also will note the p/s.

Thanks heaps!

 

[Shane Devenshire]

Hi,

And yet another way which may seem more intuitive:

=SUMIF(A1:C1,"<>#DIV/0!")

In the case of tm's formula the idea is to make the second argument larger
than anything in the range, so if you know you number will be smaller than
9,000,000,000 you can use:

=SUMIF(A1:C1,"<9E9")

or if you know the largest number will be 8 you could use

=SUMIF(A1:C1,"<9")

and so on.