IF Range is " "

slam

Well-known Member
Joined
Sep 16, 2002
Messages
848
Office Version
  1. 365
  2. 2019
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

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.
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.
 
Upvote 0
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")
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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:
Upvote 0
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:
Upvote 0

Forum statistics

Threads
1,213,565
Messages
6,114,337
Members
448,568
Latest member
Honeymonster123

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