How to Count Number of Occurrences in Excel

This guide will discuss how to count the number of occurrences in Excel using the UNIQUE and COUNTIF functions.

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

  • The UNIQUE function returns a list of unique values in the selected list or range. Additionally, these values can be numbers, times, dates, text, etc.
  • When the array is the final result, the returned array values will become a range that automatically updates when new unique values are added or removed from the original source range.
  • The UNIQUE function is not case-sensitive. For example, the function will take “ORANGE” and “orange” as the same text.

Excel is an excellent tool to use for different situations and purposes. Since it has several built-in functions and tools, we can easily perform difficult and complex tasks in Excel. For instance, we can easily manipulate and organize values from our data set.

In this guide, we will focus on learning how to count the number of times a certain value occurs in the data set. Luckily, we can easily determine the number of times a value appears in a range or array in Excel using the different built-in functions. 

Firstly, we can use the UNIQUE function to identify the unique values in the data set. Then, we will utilize the COUNTIF function to count the number of occurrences of each unique value in the data set. 

Let’s take a sample scenario wherein we need to count the number of occurrences in Excel.

Suppose you are spearheading a team-building event in the company. Let’s say there is a rule that states an employee can only participate in two games. So you need to verify the game participant list to ensure that an employee’s name does not occur more than twice. 

To do this, you used the UNIQUE function to obtain the unique text values in the data set, which would give you the names of the employees who signed up. Then, you used the COUNTIF function to count the number of occurrences each name appeared in the data set. 

Before we move on and dive into the steps of how to count the number of occurrences in Excel using the UNIQUE and COUNTIF functions, we will first learn the syntax of the different functions we will use. 

 

The Anatomy of the UNIQUE Function

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

=UNIQUE(array, [by_col], [exactly_once])

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

  • = the equal sign is how we start any function in Excel.
  • UNIQUE() is our UNIQUE function. The function returns the unique values from the selected range or array.
  • array is the only required argument. So this refers to the range or array from which we want to return the unique rows or columns.
  • by_col is an optional argument. And this refers to a logical value. By default or FALSE, the function will compare rows against each other and return the unique rows. When we input TRUE, the function will compare columns against each other and return the unique columns.
  • exactly_once is another optional argument. So this refers to a logical value. The function will return all distinct rows or columns from the array by default or FALSE. Otherwise, TRUE, the function will return rows or columns that occur exactly once from the array.

 

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. The function is used to count the number of cells within a range that meet the condition we set.
  • range is a required argument. So this refers to the range of cells from which we want to count the non-blank cells.
  • criteria is another required argument. The argument refers to the condition that can be in the form of a number, expression, or text that will define which cells will be counted.

Great! Now we can dive into a real example of counting the number of occurrences in Excel.

 

A Real Example of Counting Number of Occurences in Excel

Let’s say we have a data set containing the names of employees who signed up for different games in the team-building event. So our initial data set would look like this:

Initial dataset

 

Since we are tasked to ensure that each employee has only signed up to participate in only one or two events maximum, we need to determine the number of times an employee’s name appears in the data set. 

Firstly, we can utilize two built-in functions in Excel to easily determine the number of occurrences for specific data or values in a data set. In this case, we will utilize the UNIQUE and COUNTIF functions.

So the UNIQUE function is used to return a list of unique values from the selected range or array. And the COUNTIF function is used to count the number of cells that met a condition or criteria we set.

To apply this method, we will first use the UNIQUE function to get a list of the unique values from our data set. Once we have our list of unique values, we will use the COUNTIF function. So we will set the COUNTIF function to only count the cells in our data set containing the unique values. 

Thus, it will return the number of times the unique value appeared in the data set. Finally, we can count the number of occurrences of each unique value in our data set. 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 discuss the steps of how to count the number of occurrences in Excel using the UNIQUE and COUNTIF functions.

 

How to Count Number of Occurences in Excel

In this section, we will discuss the step-by-step process of how to count the number of occurrences in Excel using the UNIQUE and COUNTIF functions. Furthermore, each step contains detailed instructions and pictures to help you along the way. 

To use this method, we can simply follow the steps below.

1. Firstly, we will get the list of unique values. To do this, we can simply type in the formula “=UNIQUE(B2:B10)”. Then, we will press the Enter key to return the result.

Count Number of Occurrences in Excel

 

2. Secondly, we will count the number of occurrences. To do this, we can simply input the formula “=COUNTIF($B$2:$B$10,E2)”. Lastly, we will press the Enter key to return the result.

Count Number of Occurrences in Excel

 

3. Thirdly, we will drag down the Fill Handle tool to copy the formula.

Count Number of Occurrences in Excel

 

4. And tada! We have successfully counted the number of occurrences in Excel.

Count Number of Occurrences in Excel

 

And that’s pretty much it! We have successfully discussed how to count the number of occurrences in Excel using the UNIQUE and COUNTIF functions. Now you can apply this method to your work whenever you need to determine the number of occurrences of values in your data set.

Are you interested in learning more about what Excel can do? You can now use the UNIQUE 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