How to Count Names in Excel

This guide will explain how to count names in Excel using the COUNTIF function

The rules for using the COUNTIF function in Excel are the following:

  • The COUNTIF function counts the number of cells within a range that meet the criteria or conditions we set. 
  • The function only supports a single condition. Additionally, it is not case-sensitive. 
  • Furthermore, the function supports logical operators such as <, >, <>, <=, and >=. The function also supports wildcards such as * and ? for partial matching. 
  • Each condition needs a separate range and criteria argument. Moreover, operators in each criterion must be enclosed in double quotations. 
  • The function requires an actual range for the range argument. So we cannot input an array.
  • When we reference a different workbook that is closed, the function will return a #VALUE error. 

Since it contains several built-in functions and tools, Excel is a popular tool for different situations and purposes. For instance, we can use Excel as an inventory to store and list large quantities of data. 

Furthermore, we can utilize the built-in functions in Excel to easily locate or count specific names in our data set. In this case, we will focus on counting names in Excel using the COUNTIF function. 

So the COUNTIF function is one built-in function in Excel used for counting. And there are other types of counting functions in Excel, such as the COUNT function and the COUNTIFS function.

Let’s take a sample scenario wherein we need to count names in Excel.

Suppose you created an inventory of products in Excel. And you want to keep track of certain products in the data set. To do this, you simply used the COUNTIF function to count the product names you inputted to determine how many are in the inventory. 

Before we move on to a real example of counting names in Excel, let’s first learn the syntax of the COUNTIF function.

 

The Anatomy of the COUNTIF Function

The syntax or the way we write the COUNTIF function is as follows:

=COUNTIF(range, criteria)

Let’s take apart this formula and understand what each term means:

  • = the equal sign is how we begin any function in Excel.
  • COUNTIF() refers to our COUNTIF function. And this is used to count the number of cells within a range that match or meet the conditions or criteria we set.
  • range is a required argument. And this refers to the range of cells from which we want to count the non-blank cells.
  • criteria is another required argument. So this refers to the condition in the form of a number, expression, or text that will define which cells will be counted.

Note: The COUNTIF function only supports one condition. If we want to input more conditions, we should use the COUNTIFS function. 

Great! Now we can move on and dive into a real example of counting names in Excel.

 

A Real Example of Counting Names in Excel

Let’s say we have a data set containing the different products available in our inventory. And we want to count the names of specific products still available in our data set. So our initial data set would look like this:

Initial dataset

 

To do this, we will be using the COUNTIF function. So the COUNTIF function will return the count or number of matches found in the data set. For instance, we will first set the condition or criteria to find in the data set. Then, the COUNTIF function will look for a match in the selected range.

Next, it will return a count or the number of matches found in the data set. And this is how we can count names in Excel. Although, there can be a few limitations in using the function.

Firstly, the function only supports one condition. However, we can work around that by using the plus symbol and combining two COUNTIF arguments. Secondly, we can only provide an actual range and not an array as an argument. Lastly, the function is not case-sensitive.

However, we can provide different criteria in the function as it supports logical operators and wildcard characters. So we can use logical operators to set conditions on greater than, less than, equal to, less than or equal to, and greater than or equal to values. 

There are several situations we will encounter when we count names in Excel. Firstly, we can count cells using the exact name. This means that we have an exact and specific name we are looking for to count. 

Secondly, we can also count using a partial name. In this case, we need to utilize a wildcard character like an asterisk. Since we do not know the entire name, we would supplement the missing name with a wildcard character and then input the partial name we know.

Lastly, we can also count using one of several names. In this case, we are counting cells that contain two different names. So the function will count any cell with at least one of the names we input. 

So our final data set would look like this:

Final dataset

 

You can make your own copy of the spreadsheet above using the link attached below. 

Amazing! Now we can proceed and explain the steps of how to count names in Excel.

 

How to Count Names in Excel

In this section, we will explain the step-by-step process of how to count names in Excel in three different situations. Additionally, each step contains detailed instructions and pictures to guide you along the way.

To apply this method, simply follow the steps below.

1. Firstly, we will count using an exact name. To do this, we will first create an area to input the result. Then, we can simply type in the formula “=COUNTIF(B2:B9, “black ballpen”)”. Lastly, we will press the Enter key to return the count.

Count Names in Excel

 

2. And tada! We have counted an exact name in Excel.

Final result

 

3. Secondly, let’s try counting using a partial name. In this case, we will use a wildcard character, specifically the asterisk, in place of the missing name. So we will input the formula “=COUNTIF(B2:B9, “*bag”)”. Then, we will press the Enter key to return the result.

Count Names in Excel

 

4. And tada! We have successfully counted a partial name in Excel.

Final output

 

5. Thirdly, let’s try counting using one of several names. In this case, we will combine two COUNTIF functions using the plus symbol. To do this, we will type in the formula “=COUNTIF(B2:B9, “*ballpen”) + COUNTIF(B2:B9, “*bag”)”. Lastly, we will press the Enter key to return the count.Count Names in Excel

 

6. And tada! We have successfully counted names in Excel.

Count Names in Excel

 

And that’s pretty much it! We have successfully explained how to count names in Excel using the COUNTIF function. Now you can simply choose any method that is appropriate to your data set and apply it to your work. 

Are you interested in learning more about what Excel can do? You can now use the COUNTIF function and the various other Microsoft Excel formulas available to create great worksheets that work for you. Make sure to subscribe to our newsletter to be the first to know about the latest guides and tutorials from us.

Get emails from us about Google Sheets.

Our goal this year is to create lots of rich, bite-sized tutorials for Google Sheets users like you. If you liked this one, you'll love what we are working on! Readers receive ✨ early access ✨ to new content.

0 Shares:
Leave a Reply

Your email address will not be published. Required fields are marked *

You May Also Like