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.
How to Count Specific Names in Excel: 3 Methods
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 (criteria argument).
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.
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. 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 (text2 argument).
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 (within_text argument).
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
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.
Download Practice Workbook
Conclusion
To conclude, the 3 simple methods mentioned above will help you to count the specific names in Excel.
