IF AND OR Confusion!

[robbarba]

Hi Team,

In advance thanks for your help.

I have a group of four cells Y, Z, AA, and AB that can contain one of 3 values "Completed", "Waiting" or "Not App"
If all four cells = "Completed" or all four cells = "Not App" then in Cell AE say "Validated"
If any of the four cell contain any other data i.e. "Waiting" then in Cell AE say "Pending"

This is what I have so far:

=IF(AND(Y2="Not App",Z2="Not App",AA2="Not App",AB2="Not App")*OR (Y2="Completed",Z2="Completed",AA2="Completed",AB2="Completed"),"Validated","Pending")

my data set: Y2="Completed",Z2="Completed",AA2="Completed",AB2="Completed"

in cell AE2 I get the result "Pending"

Assistance is appreciated!

 

[Joyner]

Hello, try:

=IF(OR(AND(Y2="Not App",Z2="Not App",AA2="Not App",AB2="Not App"),AND(Y2="Completed",Z2="Completed",AA2="Completed",AB2="Completed")),"Validated","Pending")

 

[Eric W]

Also:

=IF(OR(COUNTIF(Y2:AB2,{"Completed","Not App"})=4),"Validated","Pending")

 

[robbarba]

Thank you both, Never thought of using a COUNTIF in there, thanks again !!

 

[Joyner]

Also:

=IF(OR(COUNTIF(Y2:AB2,{"Completed","Not App"})=4),"Validated","Pending")

Cleaner, I guess I was thinking that would be an array formula

 

[robbarba]

SPEEDBUMP Cell AA can also be singularly marked as "Completed" with no adverse effect is there a way to add this as another OR?

 

[Joyner]

SPEEDBUMP Cell AA can also be singularly marked as "Completed" with no adverse effect is there a way to add this as another OR?

What do you mean - AA is considered in the formula, do you mean if AA2 alone is marked "Completed" the formula should return "Validated"

 

[Eric W]

Not sure what you're asking. What results would you want in the following cases:

	Y	Z	AA	AB	AC	AD	AE
completed	completed	completed	completed	Validated
completed	Pending
not app	not app	completed	not app	Pending
waiting	waiting	completed	not app	Pending
waiting	completed	Pending

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]2[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]3[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]5[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]6[/TD]

[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

Sheet1​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]AE2[/TH]
[TD="align: left"]=IF(OR(COUNTIF(Y2:AB2,{"Completed","Not App"})=4),"Validated","Pending")[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]



Joyner: technically that is an array formula, but sometimes when you give it an explicit array constant (the {} ), you don't need to use Control+Shift+Enter.

 

[robbarba]

thanks for your assistance in advance,

Line 2 - Validated
Line 3 - Pending
Line 4 - Validated
Line 5 - Pending
Line 6 - Pending
All Blank - Pending
All Completed - Completed
All Not App - Completed

 

[robbarba]

Yes, that is correct.