IF 2 Cells are blank, then " "

JumboCactuar

Well-known Member
Joined
Nov 16, 2016
Messages
785
Office Version
  1. 365
Platform
  1. Windows
Hi,
im trying to keep a cell blank if 2 other cells are also blank

example:
A1 B1 - both blank

C1
Code:
[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]
 

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.
Is there a space in A1 or B1? If there is then it really isn't blank.
 
Upvote 0
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
 
Last edited:
Upvote 0
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,""),""))
 
Last edited:
Upvote 0
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,""),"")
 
Upvote 0
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,""))
 
Last edited:
Upvote 0
... 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))
 
Upvote 0

Forum statistics

Threads
1,213,510
Messages
6,114,044
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top