Check ready when two columns match requirements

[Abbioka]

Dear all,

I am looking today for a simple function that I cannot recall.

Please see attached the corresponding book with expected C2:E2 results

Context :
Clients ask for multiple items that must be prepared.
Name is noted down, and a "x" to the corresponding row of the item.
When item is prepared, "yes" is noted down.
When all "x"s of the client are "yes", "R" is put under the clients name to show all items are ready.

In the file we use everyday, more than 100 items are listed and we usually use filter and manually check "x"s and manually type "R", but it takes time.

I have tried an IF function but when the first "yes" shows, it reports "R" but does not take into consideration the second "x" being not yet "yes".

I hope I was clear enough, and thank you in advance.

[Abbioka]

I think I made it work with

=IF(COUNTIFS(D3:D8,"x",$B$3:$B$8,"Yes")=COUNTIF(D3:D8,"x"),"R","F")

[AliGW]

Thanks for letting us know.

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

[Abbioka]

I have one issue,

When I extend the formula where there is neither "x" or "yes" (blank space, ready to be filled) it returns "R" as the amount of x and yes is matching (0).
What could I add to make it only start at higher than 1 ?

[AliGW]

Is this what you mean?

=IF(COUNTIFS(D3:D8,"x",$B$3:$B$8,"Yes")=COUNTIFS(D3:D8,"x",$B$3:$B$8,"<>"),"R","F")

If not, attach an updated sample.

A good sample workbook has just 10-20 rows of representative data that has been desensitised. It also has expected results mocked up, relevant cells highlighted and a few explanatory notes.

[AliGW]

Perhaps this would be more efficient?

=IF(COUNTIFS(D$3:D$11,"x",$B$3:$B$11,"")>0,"F","R")

[AliGW]

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

Also, if you have not already done so, you may not be aware that you can thank those who have helped you by clicking the small star icon (* Add Reputation) located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of all those who offered help.

[Abbioka]

Thank you for your answer,

I have updated the folder with instructions.
Still shows R when no X are present.
Still shows R after removing one X
Wanted :
Shows "" (blank) when no X on the column
Turns from R to F when one X is removed

Thank you.

[AliGW]

Turns from R to F when one X is removed

This criterion is impossible with a formula. Excel cannot 'remember' that there was once an 'x' there. You are going to need VBA - shall I move the thread for you?

This is the closest you can get with a formula:

=IF(COUNTIF(C$3:C$12,"x")=0,"",IF(COUNTIFS($B$3:$B$12,"YES",C$3:C$12,"x")=COUNTIF(C$3:C$12,"x"),"R","F"))

[Abbioka]

Worked, AND was missing at the start to make >0 work

=IF(AND(COUNTIF(C3:C12,"x")=COUNTIFS(C3:C12,"x",$B3:$B12,"Yes"),(COUNTIF(C3:C12,"x")>0)),"R","")

[AliGW]

Glad to have helped.

If you have not already done so, you may not be aware that you can thank those who have helped you by clicking the small star icon (* Add Reputation) located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of all those who offered help.