Logical Array Formula

[mark9988]

Hello everyone,

I'm hoping you can help me solve a formula that I'm struggling with...

My file includes Column A where customer names reside and column B includes service satisfaction scores that are provided by the customer.

For every service opportunity, a corresponding rank is provided by the customer and subsequently entered in column B. There are three available options when a customer ranks their service; they include: "satisfied", "unsatisfied", "unsure".

Column C is where I'm hoping to have my formula that basically returns a score based on scores residing in column B.

For instance, in my example below, "John Smith" had 3 service calls. His satisfaction scores in column B include one "unsatisfied" ranking. As a result, I would need my formula in column C to show "Unsatisfied" in each row where John Smith is listed.

Basically, one "unsatisfied" in column B will always result in "unsatisfied" in column C. If a customer has a "satisfied" and/or "unsure" ranking in column B, it will always result in "Satisfied" in column C.


Column A, Column B, Column C
John Smith, Satisfied, Unsatisfied
Mary Wilson, Satisfied, Satisfied
John Smith, Unsatisfied, Unsatisfied
John Smith, Not Sure, Unsatisfied
Mary Wilson, Not sure, Satisfied


Hope this makes sense and thank you all once again

Mark9988

 

[sheetspread]

Assuming your data starts in A1:


=IF(SUMPRODUCT(--($A$2:$A$6=A2),--($B$2:$B$6="Unsatisfied"))>=1,"Unsatisfied","Satisfied")

in C2

 

[mark9988]

Thank you Sheetspread, your formula works great!