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]

If I understand correct, I think this:

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

Or this depending on what your requirements are -I'm not for sure:

=IF(OR(AND(AA2="Completed",COUNTIF(Y2:AB2,{"Completed","NotApp"})=3),COUNTIF(Y2:AB2,{"Completed","NotApp"})=4),"Validated","Pending")

 

[Marcelo Branco]

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

Shouldn't the values in red be Validated?

M.

 

[Joyner]

Okay, so I could only get these two to work, again the one you use depends on your actual required criteria:

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

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

 

[robbarba]

I've tested both and option #1 works as written giving the required results.

Thank you

 

[robbarba]

Validated is corrected, I have completed on the brain !!

 

[robbarba]

Guys,

After testing (entering 628 rows) I found that the following to be true

Completed Completed Completed Completed
Not App Not App Completed Not App
Not App Not App Completed Completed
Not App Not App Not App Not App


if you could explain the logic to make the necessary updates!

 

[Joyner]

Guys,

After testing (entering 628 rows) I found that the following to be true

Completed Completed Completed Completed
Not App Not App Completed Not App
Not App Not App Completed Completed
Not App Not App Not App Not App


if you could explain the logic to make the necessary updates!

What updates? Can you explain further? Do you want all of those listed above to be True and say "Validated"?

If not, then specifically show the cases that should be true.

 

[robbarba]

that is correct, ONLY these conditions are TRUE

 

[Eric W]

Maybe:

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

 

[robbarba]

testing the above formula now, let you know after a few hundred entries!