# IF Range is " "

#### slam

##### Well-known Member
I am struggling to get an IF OR to work where one condition is if A4 is " " display " " and the other is if the range B5:AO is " " display " "

Is it a COUNT I need?

=IF(OR(COUNT(B4:AO4=""),A4=""),"",IF......

Thanks....

### Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.
Try the formula below (depending on you sorting out your ranges (you use B5:AO is " " display " " in your description and B4:AO4="" in your formula))

=IF(OR(COUNTIF(B4:AO4,"="&"")=ROWS(B4:AO4)*COLUMNS(B4:AO4),A4="")," ","BBB")

Replace "BBB" with what looks like you are starting another if statement.

Try the formula below (depending on you sorting out your ranges (you use B5:AO is " " display " " in your description and B4:AO4="" in your formula))

Replace "BBB" with what looks like you are starting another if statement.

Thank you for the reply. Yes, my mistake in the first post. Reference should have been B4:AO4

That appears to be correctly working when A4 is "" but not when the range B4:AO4 is ""

I seem to have got it working with the following change:

=IF(OR(COUNTIF(B4:AO4,"")=ROWS(B4:AO4)*COLUMNS(B4:AO4),A4=""),"","BBB")

Thank you for the reply. Yes, my mistake in the first post. Reference should have been B4:AO4

That appears to be correctly working when A4 is "" but not when the range B4:AO4 is ""

I seem to have got it working with the following change:

=IF(OR(COUNTIF(B4:AO4,"")=ROWS(B4:AO4)*COLUMNS(B4:AO4),A4=""),"","BBB")

Actually, ignore the "got it working" part. I see that isn't the case.

Are you 100% sure you want an OR statement?

i.e. what do you want in the following scenarios blank or "BBB"?

A4 is blank and F4 is 1
A4 is blank and F4 is blank
A4 is 1 and F4 is blank
A4 is 1 and F4 is 1

Are you 100% sure you want an OR statement?

i.e. what do you want in the following scenarios blank or "BBB"?

A4 is blank and F4 is 1
A4 is blank and F4 is blank
A4 is 1 and F4 is blank
A4 is 1 and F4 is 1

F4 in your example isn't entirely relevant by itself in some scenarios

I would describe it in this manner:

If A4 is "" the formula should always produce a "" regardless of what's in B4:AO4
If EVERY cell in the range B4:AO4 is "" the formula should always produce a "" regardless of what's in A4
If A4 is "" and if one or more cells in the range B4:AO4 is 1, the formula will produce a ""
If A4 is 1 and one or more of the cells in the range B4:AO4 is 1 the formula will produce "BBB"

So I think the first two conditions listed above mean an OR statement is correct in this formula?

Thank you

If A4 is "" the formula should always produce a "" regardless of what's in B4:AO4

If A4 is "" and if one or more cells in the range B4:AO4 is 1, the formula will produce a ""

Not sure why you listed that second one if the first one is true. Also, what if none of these conditions are true?

Anyway, this formula should work for those conditions unless none of them are true in which case it will return a "FALSE" because I didn't know what you wanted for that scenario.

=IF(COUNTIF(B4:AO4,"")>=40,"",IF(AND(A4=1,COUNTIF(B4:AO4,1)>0),"BBB",IF(A4="","")))

EDIT:

Actually now that I think about it you basically want this cell to always return a "" unless A4 is 1 and one or more of the cells in the range = 1... so you could just do:

=IF(AND(A4=1,COUNTIF(B4:AO4,1)>0),"BBB","")

Last edited:
Or
=IF(OR(SUM(LEN(B4:AO4))< 1,A4="")," ","BBB")

Which must be entered with Ctrl-Shift +Enter not just Enter.
Plus remove the space after the <

@svendiamond, I think the OP is only using 1 in the formula because that is what I asked the OP to test with, the 1 represents any non "".

I think the "" is important as I believe the OP is using formulas returning "" and not true blank cells

Last edited:
Thank you, Mark! Perfect!

Thank you, Mark! Perfect!

Not quite change
Code:
``< 1``
to
Code:
``=0``
Dunno why I went the less than 1 way (must be late)

Replies
3
Views
157
Replies
3
Views
228
Replies
2
Views
178
Replies
6
Views
170
Replies
8
Views
388

1,196,484
Messages
6,015,469
Members
441,898
Latest member
kofafa

### 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.

### Which adblocker are you using?

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

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