---

You can use the following methods to count names in Excel:

Method 1: Count Cells with Exact Name

=COUNTIF(A2:A11, "Bob Johnson")

Method 2: Count Cells with Partial Name

=COUNTIF(A2:A11, "*Johnson*")

Method 3: Count Cells with One of Several Names

=COUNTIF(A2:A11, "*Johnson*") + COUNTIF(A2:A11, "*Smith*")

Bonus Method 4: Count Names Using SUMPRODUCT()

=SUMPRODUCT(--(ISNUMBER(SEARCH("Johnson",A2:A11))))

Bonus Method 5: Count Names Using COUNTIFS() with Multiple Criteria

=COUNTIFS(A2:A11, "*Johnson*",B2:B11, ">20")

The following examples show how to use each method with the following dataset in Excel:

Example 1: Count Cells with Exact Name

We can use the following formula to count the number of cells in column A that contain the exact name “Bob Johnson”:

=COUNTIF(A2:A11, "Bob Johnson")

The following screenshot shows how to use this formula in practice:

We can see that there are 2 cells that contain “Bob Johnson” as the exact name.

Example 2: Count Cells with Partial Name

We can use the following formula to count the number of cells in column A that contain “Johnson” anywhere in the name:

=COUNTIF(A2:A11, "*Johnson*")

The following screenshot shows how to use this formula in practice:

We can see that there are 4 cells that contain “Johnson” somewhere in the name.

Example 3: Count Cells with One of Several Names

We can use the following formula to count the number of cells in column A that contain “Johnson” or “Smith” somewhere in the name:

=COUNTIF(A2:A11, "*Johnson*") + COUNTIF(A2:A11, "*Smith*")

The following screenshot shows how to use this formula in practice:

We can see that there are 6 cells that contain either “Johnson” or “Smith” somewhere in the name.

Bonus Example 4: Count Names Using SUMPRODUCT()

We can use the SUMPRODUCT() function combined with SEARCH() to count names in a more flexible way. This approach is helpful when you need to incorporate complex conditions:

=SUMPRODUCT(--(ISNUMBER(SEARCH("Johnson",A2:A11))))

The following screenshot shows how to use this formula in practice:

We can see that this formula also returns 4 cells containing “Johnson” in the name. 

Bonus Example 5: Count Names Using COUNTIFS() with Multiple Criteria

Sometimes you might need to count names that meet additional criteria. For example, let’s count all cells that contain “Johnson” AND have a value greater than 20 in column B:

=COUNTIFS(A2:A11, "*Johnson*",B2:B11, ">20")

The following screenshot shows how to use this formula in practice:

 

In this example, out of the 4 cells containing “Johnson”, only 2 of them also have a value greater than 20 in column B. This method is particularly useful when you need to filter your name counting based on additional data attributes.

Wrapping Up

Excel offers multiple ways to count names in your spreadsheets, each with specific benefits:

• Use simple COUNTIF() for exact name matches or partial matches with wildcards
• Combine multiple COUNTIF() functions to search for different names
• Use SUMPRODUCT() for more complex scenarios 
• Apply COUNTIFS() when you need to add additional filtering criteria

These techniques help you analyze name-based data efficiently without creating additional helper columns. For best results, make sure your data is consistently formatted and consider creating a named range for your data to make formulas easier to read and maintain.

Additional Resources

The following tutorials explain how to perform other common tasks in Excel:

How to Count Specific Words in Excel
How to Count Unique Values by Group in Excel
How to Use COUNTIF with Multiple Ranges in Excel