RANGE OF CELLS CONTAINS 4 TEXTS OUTPUT 3 TEXTS

[Xav3rious]

Hi, my head is spinning trying to get this to work. I have 1 column with 10 cells. (ie. A1:A10) Each cell MAY contain one of 5 texts and or a empty. ("YES" , "N/A", "NO", "NO REQUEST", "PENDING",). A11 will contain outcome. Basically I'm looking for a formula for something like this..

#VALUE!

 

[Rick Rothstein]

I think you need to repost your question... all I see is your #VALUE! for the desired formula's outcome.

 

[Xav3rious]

I think you need to repost your question... all I see is your #VALUE! for the desired formula's outcome.

Second try
(RANGE=A1:A10)

IF any cell in range contains "NO REQUEST" THEN Cell A11 output is "NO REQUEST"

IF any cells in range contain any of "NO"/"PENDING"/or are empty THEN output in A11 is "PENDING"

ELSE output of A11 is "READY"

 

[Fluff]

How about

=IF(COUNTIF(A1:A10,"No request")>0, "No Request",IF(SUM(COUNTIF(A1:A10,{"No","Pending",""})),"Pending","Ready"))

 

[Rick Rothstein]

Fluff's formula is a little more compact, but since I went through the trouble of creating it, I decided to post it...

=IF(ISERROR(MATCH("NO REQUEST",A1:A10,0)),IF(SUMPRODUCT(0+OR(A1:A10={"PENDING","NO",""})),"PENDING","READY"),"NO REQUEST")

 

[Xav3rious]

How about

=IF(COUNTIF(A1:A10,"No request")>0, "No Request",IF(SUM(COUNTIF(A1:A10,{"No","Pending",""})),"Pending","Ready"))

Worked perfectly! Thank you!

 

[Fluff]

Glad we could help & thanks for the feedback.