sum / lookup w error correction
 

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,Techni cians!BG:BG)),(LOOKUP($G2,Technicians!A:A,Technici ans!BG:BG)),""),(IF(ISNUMBER(LOOKUP($G2,Technician s!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

Replace@mailinator with @tiscali.co.uk


"Robert" wrote in message
...
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,Techni cians!BG:BG)),(LOOKUP($G2,Technicians!A:A,Technici ans!BG:BG)),""),(IF(ISNUMBER(LOOKUP($G2,Technician s!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

Wouldn't this work?

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

--
Regards,

Peo Sjoblom

(No private emails please)


"Robert" wrote in message
...
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,Techni cians!BG:BG)),(LOOKUP($G2,Technicians!A:A,Technici ans!BG:BG)),""),(IF(ISNUMBER(LOOKUP($G2,Technician s!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

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

"Peo Sjoblom" wrote:

Wouldn't this work?

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

--
Regards,

Peo Sjoblom

(No private emails please)


"Robert" wrote in message
...
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,Techni cians!BG:BG)),(LOOKUP($G2,Technicians!A:A,Technici ans!BG:BG)),""),(IF(ISNUMBER(LOOKUP($G2,Technician s!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

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)


"Robert" wrote in message
...
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

"Peo Sjoblom" wrote:

Wouldn't this work?

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

--
Regards,

Peo Sjoblom

(No private emails please)


"Robert" wrote in message
...
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,Techni cians!BG:BG)),(LOOKUP($G2,Technicians!A:A,Technici ans!BG:BG)),""),(IF(ISNUMBER(LOOKUP($G2,Technician s!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