How to Count Number of Occurrences in Excel

In this tutorial, you will learn how to count number of occurrences in Excel.

In Excel, you may count the instances of various values in a column using the UNIQUE function and COUNTIF functions.

Once ready, we’ll get started by utilizing real-world examples to show you how to count number of occurrences in Excel.

Anatomy of UNIQUE & COUNTIF Functions

UNIQUE Function


A list of distinct values in a list or range is what the UNIQUE function produces.

COUNTIF Function

=COUNTIF(range, criteria)

To count the number of cells that match a requirement, such as the number of times a specific city occurs in a client list, use COUNTIF function, one of the statistical functions.

Count Number of Occurrences

Before we begin we will need a group of data to be used to count number of occurrences in Excel.

Step 1

First, you need to have a clean and tidy group of data to work with.

Step 2

First, we need to insert a formula to find a list of unique data =UNIQUE(A2:A11).

Step 3

Then, to find the number of occurrences for each weather, we will insert this formula =COUNTIF(A2:A11,C2).

Step 4

Once you are done, your Excel will look like this.