countif with partial name match

uceaamh

New Member
Joined
Feb 23, 2017
Messages
27
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:
EmployeeReport completed on time?
HumphreyNo
ShelleyYes
HumphreyYes
DominoNo
ShelleyNo
DominoYes
DominoYes

<tbody>
</tbody>


My current formula looks like:
Code:
=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!
 

Some videos you may like

Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type

uceaamh

New Member
Joined
Feb 23, 2017
Messages
27
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

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,201
The most crucial information is lacking: What are the expected values in Sheet1 given the data of Sheet2?
 

uceaamh

New Member
Joined
Feb 23, 2017
Messages
27
I'm trying to get a percentage.

Code:
[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

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,201

ADVERTISEMENT

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?
 
Last edited:

uceaamh

New Member
Joined
Feb 23, 2017
Messages
27
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

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,201

ADVERTISEMENT

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.
 

Watch MrExcel Video

Forum statistics

Threads
1,123,295
Messages
5,600,785
Members
414,405
Latest member
Zaurb

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top