Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
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 |
#3
|
|||
|
|||
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 |
#4
|
|||
|
|||
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 |
#5
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Lookup Vector > Lookup Value | Excel Worksheet Functions | |||
Formula checking multiple worksheets | Excel Worksheet Functions | |||
Lookup Function Problem | Excel Discussion (Misc queries) | |||
Lookup function w/Text and Year | Excel Worksheet Functions | |||
double lookup, nest, or macro? | Excel Worksheet Functions |