Using IF(AND(OR STATEMENT??

[mcintoshmc]

W2:Z2 could say required, not required, or completed

If the cell says "required" it will eventually say "completed" once done. If it says "Not required", then it's not required.

I want cell AA2 to say "Completed" once all cells say "completed" or "Not required" or any combination thereof.

This is the formula that I created, but doesn't work. Any ideas?

=IF(OR(AND(W5="Completed",X5="Completed",Y5="Completed",Z5="Completed"),AND(W5="Not Required",X5="Not Required",Y5="Not Required",Z5="Not Required")),"Completed","Not Completed")

 

[Scott T]

Instead of test each one to see if they are completed or not required you can just test if any of them are required. If not then so Completed.

=IF(IFNA(MATCH("required",W2:Z2,0),0)=0,"Completed","Not Completed")

 

[Dave Patton]

=IF(COUNTIF(W5:Z5,"Completed")+COUNTIF(W5:Z5,"Not Required")=4,"Completed","Not Completed")

 

[jtakw]

Hi,

A couple of options for you:

Book1
	W	X	Y	Z	AA	AB
2	Completed	Required	Not required	Completed	Not completed	Not completed
Sheet165

Cell Formulas
Range	Formula
AA2	=IF(SUM(COUNTIF(W2:Z2,{"Completed","Not required"}))=4,"Completed","Not completed")
AB2	=IF(COUNTIF(W2:Z2,"Required"),"Not completed","Completed")

 

[mcintoshmc]

Instead of test each one to see if they are completed or not required you can just test if any of them are required. If not then so Completed.

=IF(IFNA(MATCH("required",W2:Z2,0),0)=0,"Completed","Not Completed")

That worked perfectly. Thank you.

I then did this, but it returns false instead of staying blank.

=IF($O2<>"",IF(IFNA(MATCH("required",$W2:$Z2,0),0)=0,"Completed","Not Completed"))

 

[mcintoshmc]

nevermind. I see what I did wrong ,"")

 

[mcintoshmc]

Thanks everybody for your help!