AND syntax to distinguish BLANK cells

[The Grim Discovery]

Excelites

I'm using formula to state whether or not A3:D3 are each populated with a 'yes'.

IF(AND(A3="yes",B3="yes",C3="yes",D3="yes"),"yes","no")

But I cant work out how to write the formula so that if all of those cells are blanks then the formula should produce a blank. Would appreciate your guidance.

Thanks in advance

 

[tyija1995]

Hey!

You can do it via nesting an ISBLANK function into your current formula, so it'll look like this:

IF(AND(ISBLANK(A3),ISBLANK(B3),ISBLANK(C3),ISBLANK(D3)),"",IF(B4=TRUE,"",IF(AND(A3="yes",B3="yes",C3="yes",D3="yes"),"yes","no")))

A bit messy but it should do the trick!

Kind regards,
Matt

 

[tyija1995]

Sorry, I left in part of the formula that's not needed, try this instead:

IF(AND(ISBLANK(A3),ISBLANK(B3),ISBLANK(C3),ISBLANK(D3)),"",IF(AND(A3="yes",B3="yes",C3="yes",D3="yes"),"yes","no"))

 

[The Grim Discovery]

Tyija - thanks that's nailed it. Really appreciate your help.

 

[AlanY]

Excelites

I'm using formula to state whether or not A3:D3 are each populated with a 'yes'.

IF(AND(A3="yes",B3="yes",C3="yes",D3="yes"),"yes","no")

But I cant work out how to write the formula so that if all of those cells are blanks then the formula should produce a blank. Would appreciate your guidance.

Thanks in advance

try

=IF(COUNTIF(A3:D3,"")=4,"","?")

fill in the ? when not all 4 are blank

 

[jtakw]

Hi,

Try this:

=IF(COUNTIF(A3:D3,"Yes")=4,"Yes",IF(COUNTIF(A3:D3,"")=4,"","No"))