Google Sheets need to return different values in a column based on multiple criteria in another-CodePudding

I am working in Google Sheets and I am drawing a blank. Column A are a list of criteria which may repeat any number of times. Column B are Yes/No responses to those criteria. Column C needs to return "Fully Implemented" if all answers are Yes, "Not Implemented" if all answers are "No" and "Partially Implemented" if there are a combination of yes and no responses on each instance of the question. I have attached an image that may assist. I appreciate any help.

CodePudding user response：

Try LOOKUP COUNTIF/-S:

=ArrayFormula(IF(LEN(A:A),LOOKUP((COUNTIF(A:A,A:A)=COUNTIFS(A:A,A:A,B:B,"Yes")) (COUNTIF(A:A,A:A)=COUNTIFS(A:A,A:A,B:B,"No"))*2,{0,1,2},{"Partially implemented","Fully implemented","Not implemented"}),))

CodePudding user response：

I found a solution, thank you for your help.

=@IFS(COUNTIFS(A:A,@A:A,O:O,"yes")/COUNTIF(A:A,@A:A)=1,"Fully Implemented",COUNTIFS(A:A,@A:A,O:O,"yes")/COUNTIF(A:A,@A:A)=0,"Not Implemented",AND(COUNTIFS(A:A,@A:A,O:O,"yes")/COUNTIF(A:A,@A:A)<1,COUNTIFS(A:A,@A:A,O:O,"yes")/COUNTIF(A:A,@A:A)>0),"Partially Implemented")