Using counif and index in excel

[dejsmith]

Hey guys,

I am trying to count data that spans multiple columns, but only where it matches someone's name. My issue is that I can't get the formula to count the criteria and match it to a name. For eg. Check sample sheet attached. In columns L & M has conditions of either yes or no. In column A I have sample names of workers and in Column B I have the title off projects they worked. I want the formula to identify if a worker received "yes" in Columns L & M for all the projects they've worked and return an answer. Is this possible? Not sure if I'm making sense.

[p45cal]

Two solutions in the attached.
1. On sheet Dump a formula in cell N2 copied down:

=COUNTIF($A$2:$A$65,A2)=COUNTIFS($L$2:$L$65,"Yes",$M$2:$M$65,"Yes",$A$2:$A$65,$A2)

2. On sheet Dump (2) a Power Query solution, update the table on the left with new data, then right-click the table on the right and choose Refresh.

[dejsmith]

That works! Thanks p45cal

[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, you may not be aware that you can thank those who have helped you by clicking the small star icon 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 those who helped.