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:

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Employee[/TD]
[TD]% Reports Completed on Time[/TD]
[/TR]
[TR]
[TD]Hubert Humphrey[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Mary Shelley[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Fats Domino[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

In Sheet 2:
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Employee[/TD]
[TD]Report completed on time?[/TD]
[/TR]
[TR]
[TD]Humphrey[/TD]
[TD]No[/TD]
[/TR]
[TR]
[TD]Shelley[/TD]
[TD]Yes[/TD]
[/TR]
[TR]
[TD]Humphrey[/TD]
[TD]Yes[/TD]
[/TR]
[TR]
[TD]Domino[/TD]
[TD]No[/TD]
[/TR]
[TR]
[TD]Shelley[/TD]
[TD]No[/TD]
[/TR]
[TR]
[TD]Domino[/TD]
[TD]Yes[/TD]
[/TR]
[TR]
[TD]Domino[/TD]
[TD]Yes[/TD]
[/TR]
</tbody>[/TABLE]


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:

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Employee[/TD]
[TD]% Reports Completed on Time[/TD]
[/TR]
[TR]
[TD]Hubert Humphrey[/TD]
[TD].5[/TD]
[/TR]
[TR]
[TD]Mary Shelley[/TD]
[TD].5[/TD]
[/TR]
[TR]
[TD]Fats Domino[/TD]
[TD].66[/TD]
[/TR]
</tbody>[/TABLE]

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.