countif with partial name match

[uceaamh]

Hello,

I am attempting to create a list of employees who finished reports on time. My issue is that my data sources have different interpretations of their name.

I'll give an example.

In Sheet 1:

Employee	% Reports Completed on Time
Hubert Humphrey
Mary Shelley
Fats Domino

<tbody>
</tbody>

In Sheet 2:

Employee	Report completed on time?
Humphrey	No
Shelley	Yes
Humphrey	Yes
Domino	No
Shelley	No
Domino	Yes
Domino	Yes

<tbody>
</tbody>

My current formula looks like:

=IF("*"&Sheet2[Employee]&"*"=Sheet1!A2,COUNTIF(Sheet2[Report completed on time?],"yes")/SUM(COUNTIF(Sheet2[Report completed on time?],"yes"),COUNTIF(Sheet2[Report completed on time?],"no")),"")

Which is not returning anything.

I know the issue lies in the partial name match.

Any suggestions?

Thanks!

 

[uceaamh]

As an addendum:

In Sheet1, the list of Employees is generated using the following formula:

=IFERROR(INDEX(Table4[Employee], MATCH(0,IF($B$2=Table4[Place], IF($B$3=Table4[Team], COUNTIF($A$6:A6,Table4[Employee]),""),""),0)),"")

Where B2 and B3 are dropdown lists.

Not sure if that effects follow on formulas, but thought I should add that info.

 

[Aladin Akyurek]

The most crucial information is lacking: What are the expected values in Sheet1 given the data of Sheet2?

 

[uceaamh]

I'm trying to get a percentage.

[COLOR=#333333]=COUNTIF(Sheet2[Report completed on time?],"yes")/SUM(COUNTIF(Sheet2[Report completed on time?],"yes"),COUNTIF(Sheet2[Report completed on time?],"no"))[/COLOR]

successfully returns a % of "Yes" responses in the entire Sheet2[Report Completed on Time?] column.

So I'm attempting to add a criteria to that, limiting the numerator to "Yes" for a person, and the denominator to "Yes"+"No" for the same person.

Thanks for responding @Aladin Akyurek

 

[Aladin Akyurek]

I'm trying to get the values you want to see under "% Reports Completed on Time" for the data posted... Care to provide those values?

 

[uceaamh]

In this example, the values I'm looking for would be:

Employee	% Reports Completed on Time
Hubert Humphrey	.5
Mary Shelley	.5
Fats Domino	.66

<tbody>
</tbody>

For the 1 yes/ 1 no connected to Humphrey, 1 yes/ 1 no for Shelley, and 2 yes / 1 no for Domino.

I can't get the formula that I'm putting in that column to use name as a criteria however.

Thanks again

 

[Aladin Akyurek]

In B2 of Sheet1 enter and copy down:

=SUMPRODUCT(COUNTIFS(A2,"*"&Sheet2!$A$2:$A$8&"*"),--(Sheet2!$B$2:$B$8="Yes"))/SUMPRODUCT(COUNTIFS(A2,"*"&Sheet2!$A$2:$A$8&"*"))

The range references, I trust, are obvious.

 

[uceaamh]

That got it. Thanks Aladin! That roadbump was taking my whole day.

 

[Aladin Akyurek]

That got it. Thanks Aladin! That roadbump was taking my whole day.

Glad to help.