Another sum Formula

Posted by LARS on August 21, 2001 11:33 AM

I have vlookup's in Column I, X, Y and in column Z i want all three added up. The problem is if I or X or Y are blank or return no value then the sum formula in column Z returns blank

here is the formula I have in column Z
=if(iserror(i6+x6+y6),"-"(i6+x6+y6))

What do I replace the "-" with to get a number in column Z if any one of the I6, X6 or Y6 is blank to still add the other two that have numbers. Maybe only one has a number I want that number in column Z even if the other two don't retunn a value from the lookup

Thanks
Lars

Posted by Aladin Akyurek on August 21, 2001 11:40 AM

Lars,

Let those VLOOKUP formulas just return "" or 0 and use SUM as usual.

Aladin

Posted by LARS on August 21, 2001 11:45 AM

My boss wants the damn "-" to show if no value is returned from the lookups. that's the problem..is there anyway to show the "-" and still have the formula to work?

Posted by Aladin on August 21, 2001 11:46 AM

Or use:

=if(isnumber(i6),i6,0)+if(isnumber(x6),x6,0)+if(isnumber(y6),y6,0),

since there are 3 cells that you want to sum.

Aladin

Posted by Aladin Akyurek on August 21, 2001 11:48 AM

See my second post. (NT)

, : Let those VLOOKUP formulas just return "" or 0 and use SUM as usual. : Aladin :

Posted by Lars on August 21, 2001 11:54 AM

Re: Or use: Worked perfeclty thanks Aladin!!!

Posted by Lars on August 21, 2001 11:58 AM

Re: See my second post. (NT)