While investigating large datasets in Excel, you might be required to count the number of cells containing a specific name. This article provides 3 simple methods on how to count specific names in Excel.

**Download Practice Workbook**

**3 Methods to Count Specific Names in Excel**

Let’s say we have a dataset that depicts the **Employee ID**, the **Year**, and lastly the **Sales Person of the Year **respectively. We’ll use the dataset (in **B4:D14** cells) shown below for the first and second methods.

Luckily, you can count the occurrence of a name in a worksheet in many ways. Let’s explore the various methods and how to apply them.

**1. Using COUNTIF Function to Count Exactly Matching Names**

Microsoft Excel has a built-in **COUNTIF function** to count the number of cells within a range that meet the given condition. In the example below, a list of *Sales Person of the Year* is given in the **D4:D14** cells and we want to count the number of times the name **Mathew Smith **occurs in this list.

To allow users to count any desired name instead of entering it directly into the formula, we have designated a cell to enter the name. For instance, the name is entered in the **G4** cell. So, the formula in the G5 cell will be like the following.

`=COUNTIF(D5:D14,G4)`

Here, the **D5:D14 **cells represent the *Sales Person of the Year *(** range** argument), and the

**G4**cell denotes

*Mathew Smith*(

**argument).**

*criteria***Formula Breakdown**

- In this formula, the
**COUNTIF**function takes two arguments*range*and*text*. - The
**COUNTIF**function matches the name**Mathew Smith**in the lookup array (**D5:D14**) and returns the number of counts.

**Read More:** **How to Count Words in Excel with Formula (2 Handy Examples)**

**2. Applying Wildcard Character to Count Specific Names**

The previous method matches the given criteria exactly. As a note, if at least one different character is present inside the cell, like, a space character then it will not be treated as an exact match. Simply put, the cell will not be counted.

To count the cells which contain other texts in addition to the specific name, we’ll use the **Wildcard Character**. Simply place an asterisk (*) character along with the cell reference. By varying the position of the asterisk character, we can count the name from the cells in the range. Let’s see it in action.

**2.1 If Cell Contains Specific Name at the Start **

If the specific word is at the **start **of the cell then we need to append the asterisk character after the cell reference, as illustrated in the example below.

Thus, the formula in the G5 cell will be as follows.

`=COUNTIF(D5:D14,G4&“*”)`

**2.2 When the Particular Name Is in the Middle**

In contrast, when the specific word is in the **middle **of the cell, we add the asterisk character both before and after the cell reference.

Subsequently, the formula for the G5 cell will be as follows.

`=COUNTIF(D5:D14,“*”&G4&“*”)`

**2.3 If the Specific Name Is at the End**

Lastly, if the target name is located at the **end** of the cell, the asterisk character is concatenated before the cell reference which

Eventually, the formula for the G5 cell will be as follows.

`=COUNTIF(D5:D14,“*”&G4)`

**Read More:** **Excel Formula to Count Specific Words in a Cell (3 Examples)**

**3. Utilizing SUMPRODUCT Function to Count Specific Names in Excel**

The **SUMPRODUCT** function is employed in the scenario when we have to count the name while considering both uppercase and lowercase characters.

Assuming, we have a table which shows the **Employee ID**, the **Department **they are employed in, and finally the **Employee Name.** We can use the dataset (in **B4:D14** cells) to demonstrate our third method.

**3.1 Applying SUMPRODUCT Function to Match Exact Name (Case-Sensitive)**

We will employ the **SUMPRODUCT** function in conjunction with the **EXACT** function to count the number of cells containing the specific name.

`=SUMPRODUCT(--EXACT(G4, D5:D14))`

Here, the **G4 **cell denotes *Smith *(** text1** argument) and the

**D5:D14**cells represent the

*Employee Name*(

**argument).**

*text2***Formula Breakdown**

- Here, the
**EXACT**function compares two strings of texts and returns true if they are an exact match. The*double hyphen*mark forces the TRUE and FALSE values to 1’s and 0’s. - Next, the
**SUMPRODUCT**function returns the sum of all the 1’s in the corresponding range which represents the number of matches.

**3.2 Using the SUMPRODUCT Function to Partially Match Name (Case-Sensitive)**

To locate the desired name, anywhere in the cell we need to utilize 3 functions **SUMPRODUCT**, **ISNUMBER**, and **FIND****. **

`=SUMPRODUCT(--(ISNUMBER(FIND(G4, D5:D14))))`

Here, the **G4 **cell denotes *Smith *(** find_text** argument) and the

**D5:D14**cells represent the

*Employee Name*(

**argument).**

*within_text***Formula Breakdown**

- Firstly, the
**FIND**function gives the position (as numbers) of a text inside a string. - Secondly, the
**ISNUMBER**function handles these numbers returned by the**FIND**function. The*double unary mark*(hyphen) converts the TRUE and FALSE values to ones and zeros. - Thirdly, the
**SUMPRODUCT**function adds up all the 1’s which represents the number of matches.

#### 3.3 Employing SUMPRODUCT Function to Count Names (Case-Insensitive)

To develop a case-insensitive formula for counting the names located anywhere in the cell we’ll need the **SUMPRODUCT, ISNUMBER, **and **SEARCH** functions.

`=SUMPRODUCT(--(ISNUMBER(SEARCH(G4, D5:D14,))))`

**Formula Breakdown**

- Firstly, the
**SEARCH**function determines the location (as number) of a text inside a string. - Next, the
**ISNUMBER**function converts the numbers provided by the**SEARCH**function to ones and zeros. - Lastly, the
**SUMPRODUCT**function returns the number of counts.

**Read More:** **How to Count Specific Words in a Column in Excel (2 Methods)**

**Things to Remember**

- The
**COUNTIF**function returns integer output. - The
**COUNTIF**function does not count the cells with non-numeric values, like text or**#NA.** **COUNTIF**function is unable to count specific numbers within a number like “123” from “4546123”- For columns with a mixture of text and numbers, the
**COUNTIF**function gives the wrong count.

**Conclusion**

To conclude, the 3 simple methods mentioned above will help you to count the specific names in Excel. If you have any questions or feedback, please let us know in the comment section, below or you can check out our other articles on the **ExcelDemy** website.