Updated June 9, 2023
Overview of Count Names in Excel
Excel has multiple functions, and one of its practical uses is counting names from a large dataset. However, when dealing with a lengthy list of data, counting names can be time-consuming. Fortunately, Excel offers COUNT and other related functions to count names in Excel and simplify the overall process.
Count names in Excel means using functions that help you count how many times a name appears in a table or range of cells.
The article will include information about the following functions. Along with each function, we have provided clear examples to help understand how they work.
How to Count Names in Excel?
We will learn to count names in Excel by starting with basic examples and gradually moving on to more complex ones. We have also provided an Excel template with all the solved examples for reference.
Example #1 Counting Names with Age Data
The table below contains a list of names and their corresponding age. However, it contains errors and blank cells too. Therefore, we will use the COUNT function to determine the total number of cells that contain only age.
Solution:
Step 1: Open MS Excel and create a data table with Name and Age columns (Columns A and B, respectively
Step 2: Create a new column heading “COUNT Name” in cell D2.
Step 3: In cell D3, enter the formula =COUNT(B2:B10).
The formula will count the number of cells containing only numbers and ignore texts and other values.
Step 4: Press Enter key to get the result.
Example #2 Counting Names Having a Common String
The below table lists some names with their age. We want to find the count of names containing the suffix “Jr.” using the COUNTIF function.
Solution:
Step 1: Create a new column heading “COUNT Name” in cell D1.
Step 2: In cell D2 start typing the formula =COUNTIF(A2:A10)
Step 3: Now, provide the criteria by writing =COUNTIF(A2: A10,”*Jr.”) in cell D2.
Step 4: Press Enter key to see the result
Example #3 Counting the Number of Letters Ending with a Specific String
A user needs to count the number of names containing the string “ry” and having a length of 5 letters. We can achieve this using the COUNTIF function. Let’s explore how.
Solution:
Step 1: Open MS Excel and create a data table containing three columns (Columns A, B, and C) for Name, Height, and Age.
Step 2: Create a new “Count Name” column heading in cell E1.
Step 3: In cell E2, begin writing the formula with =COUNTIF(
Step 4: Provide the range A2 to A10 by writing =COUNTIF (A2: A10,
Step 5: To narrow down the search and only count names with 5 letters that contain the string “ry,” enter the formula =COUNTIF(A2:A10, “???ry”) in cell E2.
Step 6: Press Enter key to see the result.
Example #4 Using COUNTA Function
The following table contains data on 10 equipment pieces tested to check their output. We want to count the number of outcomes using the COUNTA function in Excel.
Solution:
Step 1: In an empty cell (Cell F6), enter the formula =COUNTA(C6:C15)
The formula counts the total number of non-empty cells from C6 to C15.
Step 2: Press Enter to get the result (see the image below)
Example #5 Using the COUNTIF Function
The table below has information about students, including their roll numbers and marks. We want to determine how many students scored above 60 using the COUNTIF function.
Solution:
Step 1: In an empty cell (Cell F6), enter the formula =COUNTIF(C6:C14,”>60″)
The formula counts the number of cells with an integer value greater than 60.
Step 2: Press Enter key to obtain the result.
Example #6 Using COUNTIFS Function
The table below lists people with their names, gender, and city. We want to count the number of males belonging to Mexico using the COUNTIFS function in Excel.
Solution:
Step 1: In an empty cell (Cell F6), enter the formula: =COUNTIFS(B6:B12,”Male”,C6:C12,”Mexico”)
The formula first locates “Male” in the range B6:B12, then finds “Mexico” in the corresponding rows in the range C6:C12, and counts the number of rows.
Step 2: Press Enter to see the result.
Example #7 Using LEN Function
The table below demonstrates how to use the LEN function in Excel to determine the character count in various types of strings. It displays each string in the list, the formula used to calculate its length using the LEN function, the count of characters in the string, and a brief description.
For instance, the formula =LEN(A6) shows a count of 4 for the first string, “7234”, which has 4 characters.
Example #8 Using the COUNTBLANK Function
The table contains customer information, including their Invoice numbers and the total amount they have to pay. However, some cells in the table are empty, and we need to determine the number of empty(blank) cells using the COUNTBLANK function in Excel.
Solution:
Step 1: In an empty cell (Cell E6), enter the formula =COUNTBLANK(B6:C11)
The formula counts the number of blank cells within the range B6:C11.
Step 2: Hit the Enter key to check the result
Things to Remember About Count Names in Excel
- The COUNT function does not count cells containing text, logical values (TRUE or FALSE), error values (such as #VALUE! or #N/A), or blank cells. For example, if you use =COUNT(A1:A4) and one of the cells has the word “kiwi” in it, the formula won’t count it.
- In COUNTIF criteria, the asterisk (*) is a wildcard character that matches any set of characters. For instance, the formula =COUNTIF(A1:A10,”*gmail.com”) will count all the email addresses from the range A1:A10 that ends with @gmail.com.
- If you use a question mark (?) in the criteria for COUNTIF, it will match any character. For example, =COUNTIF(A1:A4, “kiw?”) will count cells that have “kiwi” or “kiwis” in them but not “kiwie” or “kiwifruit“.
- You can use the less than (<), greater than (>), or equal to (=) operators in COUNTIF to combine criteria. For example, =COUNTIF(A1:A4,” >=10″,” <20″) will count cells that have a value between 10 and 20.
Recommended Articles
The above article is a guide to Count Names in Excel. Here, we discuss how to Count Names in Excel using functions like COUNT, COUNTIF, COUNTA, LEN, and more. You may also look at the following articles to learn more –
