sum / lookup w error correction

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hello,
I simply need to lookup 2 cells on another sheet and add them together
without producing any errors if one or both of them is blank. I am using:

=SUM(IF(ISNUMBER(LOOKUP($G2,Technicians!A:A,Technicians!BG:BG)),(LOOKUP($G2,Technicians!A:A,Technicians!BG:BG)),""),(IF(ISNUMBER(LOOKUP($G2,Technicians!A:A,Technicians!BH:BH)),(LOOKUP($G2,Technicians!A:A,Technicians!BH:BH)),"")))

This will add the numbers together if there is a number in each cell, but I
get the #VALUE error if one of them is blank. The (IF ISNUMBER) portion of it
was intended to avoid errors, but is not working here. Any help appreciated...
thanks,
Robert
 
The answer to your problem is to replace the empty strings ("") with zeros
(0).

This is strange because SUM will ignore strings or empty strings returned by
formulas but chokes on =SUM("","") or even SUM(10,"")

I wonder if anyone can excplain this?

--
HTH

Sandy
(e-mail address removed)
Replace@mailinator with @tiscali.co.uk
 
Wouldn't this work?

=SUMIF(Technicians!A:A,$G$2,Technicians!BG:BG)+SUMIF(Technicians!A:A,$G$2,Technicians!BH:BH)

--
Regards,

Peo Sjoblom

(No private emails please)
 
Thank you, they both work and return 0 instead of any error messages.
However, is there a way to get a blank cell instead of 0 if the total is less
than 1?
thanks,
Robert
 
If it is just for display you can use a custom format like

General;-General;;

which will "hide" zeros

--
Regards,

Peo Sjoblom

(No private emails please)
 
Back
Top