IF 2 Cells are blank, then " "

[JumboCactuar]

Hi,
im trying to keep a cell blank if 2 other cells are also blank

example:
A1 B1 - both blank

C1

[COLOR=#333333]=IF(COUNTBLANK(A1:B1)=2,"",A1+B1)

ive tried the above but it just displays #value if 1 of the cells is blank

any help appreciated[/COLOR]

 

[wwbwb]

Is there a space in A1 or B1? If there is then it really isn't blank.

 

[JumboCactuar]

Is there a space in A1 or B1? If there is then it really isn't blank.

both blank, they pull data from another table if available

if i simply use:
=COUNTBLANK(A1:B1)

it counts the blanks correctly

 

[wwbwb]

Highlight A1 and B1, right click and clear contents. What happens?

 

[Rick Rothstein]

both blank, they pull data from another table if available

So your cells are not actually blank then, rather, they contain formulas which display either a number or the empty text string "" instead, correct? If so, give this formula a try instead...

=IFERROR(A1+B1,IF(ISNUMBER(A1)=A1,IF(ISNUMBER(B1),B1,""),""))

 

[Rick Rothstein]

So your cells are not actually blank then, rather, they contain formulas which display either a number or the empty text string "" instead, correct? If so, give this formula a try instead...

=IFERROR(A1+B1,IF(ISNUMBER(A1)=A1,IF(ISNUMBER(B1),B1,""),""))

Actually, this formula is probably better to use...

=IF(ISNUMBER(A1),IF(ISNUMBER(B1),A1+B1,""),"")

 

[JumboCactuar]

Actually, this formula is probably better to use...

=IF(ISNUMBER(A1),IF(ISNUMBER(B1),A1+B1,""),"")

Thankyou
I cant test it just yet but i assume it will work

 

[Rick Rothstein]

I cant test it just yet but i assume it will work

Hmm, maybe it will work and maybe not... it depends on what you want to happen if only one number is displayed in A1:B1. The formula I posted earlier only displays a number if both A1 and B1 have a number in them, otherwise it displays the empty text string "". If you want the formula to display the empty text string "" only when both cells are blank and to display the whatever number is displayed if only one cell has a number and to display the sum when both cells have numbers, then use this formula instead...

=IF(ISNUMBER(A1),IF(ISNUMBER(B1),A1+B1,A1),IF(ISNUMBER(B1),B1,""))

 

[Tetra201]

... If you want the formula to display the empty text string "" only when both cells are blank and to display the whatever number is displayed if only one cell has a number and to display the sum when both cells have numbers, then ...

Try this:

=IF(AND(A1="",B1=""),"",SUM(A1:B1))