Formula help #Value

[zone709]

Hi so I sue this formula below and works if 2 values are found on sheet2, but if only 1 value is found lets say in I and doesn't find value in L it returns Value. Reason why I am using =0,text is because if it finds nothing I need it to return blank rather then 0.



=IF(Sheet2!I8=0,TEXT(Sheet2!I8,""),Sheet2!I8)+IF(Sheet2!L8=0,TEXT(Sheet2!L8,""),Sheet2!L8)

 

[BobUmlas]

You're adding text to a nnumber. Example: ""+4 which would give #VALUE !
Try:
=SUM(IF(Sheet2!I8=0,TEXT(Sheet2!I8,""),Sheet2!I8),IF(Sheet2!L8=0,TEXT(Sheet2!L8,""),Sheet2!L8))

 

[philwojo]

Why not try this then:

=if(or(Sheet2!I8=0,Sheet2!L8=0),"",I8+L8)

 

[zone709]

for some reason i tried both and thanks for the reply. I still get #value if lets say I has a value of 40 in column I and L has nothing ill get value. I just checked sheet2 also to mkae sure the columns are not text and they are not. i dont know

 

[zone709]

Phil also i see yours gives you #value if both have values weird

 

[philwojo]

Do you have formulas in your Sheet2 cells of I8 and L8, or are you typing those values into those cells?

My formula should work based on values, but if there is text in them, or a formula that is returning text and not values it could be causing this then.

 

[zone709]

But if you click your formULA right I and L cells higlight on same sheet not off of sheet2

 

[philwojo]

Sorry, got busy at work, overlooked something, try this version.

=IF(OR(Sheet2!I8=0,Sheet2!L8=0),"",Sheet2!I8+Sheet2!L8)

 

[zone709]

thats ok thanks for trying to help me. So i tried this almost there, but now if there is value in one and not the other is goes blank. So i tried it on a value of 40 in column I and nothing in L and it returns nothing instead it should return 40

 

[Joe4]

lets say I has a value of 40 in column I and L has nothing ill get value

If column I was the number 40, and L is TRULY blank, then adding those two cells would return 40.
If it is returning the #VALUE ! error, then column L is NOT really blank. It probably has something like a space in it.

It is easy to confirm that, like this (if it was cell L8):
=LEN(L8)
If that formula returns anything other than 0, than that cell is NOT really blank.