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

##### Table of Contents

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:

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:

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.

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.

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

4. And tada! We have successfully counted the 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.