For performing different tasks in Excel, you may need to count the occurrence number of each value. The agenda for this article is to show you how to count the number of occurrences of each value in a column in Excel. In this case, we’ll demonstrate 5 easy and quick methods regarding this problem. So, let’s go through the article to perform the task efficiently.

## Download Practice Workbook

You may download the following Excel workbook for better understanding and practice yourself.

## 5 Methods to Count Number of Occurrences of Each Value in a Column in Excel

Before proceeding to the tutorial, let’s get to know today’s dataset.

Here we have **Sales Rep**, **City**, and **Salary** columns. There are a few values that are repeated within the columns for making the examples understandable. **Sales Rep** and **City** are columns of text values and **Salary** for numbers values. This relation and dataset are for practice purposes only.

Now, we’ll count the number of occurrences of each value in a column in multiple ways using the above dataset. So, let’s explore them one by one.

### 1. Using COUNTIF Function

Using the **COUNTIF function**, we can count the number of occurrences of each value in a column or range. It’s simple and easy. Let’s see it in action.

**📌**** Steps:**

The **COUNTIF function** counts the number of cells within a range comparing a particular condition.

Let’s write the formula for counting the **Sales Rep** in our example Excel workbook

`=COUNTIF(B7:B23,F7)`

Within the **COUNTIF function**, we inserted all the values of **Sales Rep** as ** range**. Our

**were every name, since we need to calculate the number of instances for every name. So as criteria we have inserted a name (first name in this case, gradually will check using every other name). It gave the number of occurrences for the name**

*criteria***Max**. As our data set is not a big one, you can have a quick look and find there are

**4 Max**inside that

**Sales Rep**column.

- Now, bring the cursor to the right bottom corner of cell
**E7**and it’ll look like a plus**(+)**sign. It’s the**Fill Handle**tool. - Then, double-click on it for the rest of the values.

Oh! It’s providing faulty value. We made a mistake.

We didn’t use **Absolute Reference**, so our cell references kept changing and gave the wrong output. So, we need to use **Absolute Reference** before exercising **AutoFill**.

`=COUNTIF($B$7:$B$23,F7)`

This time it provided the correct values.

But think a little bit about whether this is in a format to fetch value at the very first glance. No, this is neither providing the insides fast nor an eye pleasant form.

To make our result from where we can derive the insides faster, we can take help from Excel **Sort & Filter** feature.

- At first, select the whole
**Sales Rep**column (**B6:B23**). - Then, go to the
**Data**tab. - Here, you will find the
**Advanced**option on the**Sort & Filter**group. So, select it.

Clicking on the **Advanced** icon will lead you to the **Advanced Filter** dialog box.

- Firstly, select
**Copy to another location**. - The
**List Range**is automatically selected as we select if before. - In the
**Copy to**box, insert the cell reference where you want to paste it. In this case, we gave it as cell**F6**. - Then, make sure to check the box of
**Unique records only**. - Lastly, hit
**ENTER**or click**OK**.

Now, we can see all the unique values from the column to a separate location in the **F6:F12** range.

- Now, use the previous
**COUNTIF**formula.

You need to use the criteria from this extracted column in order to get the occurrence number for each of the values.

- Then, utilize the
**AutoFill**feature to copy the formula to the following cells.

This **COUNTIF** formula can be used for numeric values as well.

- At this time, write the formula for the
**Salary**column of our example.

`=COUNTIF($D$7:$D$23,F15)`

*Note:** Please keep in mind, from here on we will extract the unique values to separate locations using the Sort & Filter option before using any formula*.

**Read More:** **How to Count Repeated Words in Excel (11 Methods)**

### 2. Utilizing SUM and EXACT Functions

We can find out the number of occurrences for each value using **SUM** and **EXACT** functions as well.

The name says it all for the **SUM function**, it will provide you the sum for the range provided within it.

The **EXACT function** compares two values and returns **TRUE** if they are exactly the same, otherwise **FALSE**. Usually, this function is used for text values.

**📌**** Steps:**

Our formula using the **SUM** and **EXACT** function will be something like this.

**SUM(–EXACT(range,criteria))**

For a better understanding of the formula, write the **EXACT function** portion first.

- Firstly, select cell
**G7**and enter the following formula.

`=--EXACT($B$7:$B$23,F7)`

Here we have written the **EXACT function** for **Sales Rep**. Also, we used a double hyphen to convert the **TRUE/FALSE** to **0** and **1**. We can notice that it’s an array formula. You will see what it returns, for every matching it provides **1** and **0** for non-matching.

- Then the
**SUM function**operates and gives the result.

`=SUM(--EXACT($B$7:$B$23,F7))`

*Note:** Since this is an array formula you need to use CTRL + SHIFT + ENTER instead of just ENTER to operate this formula. But if you are using Excel 365, you can do the task by just pressing ENTER. And after entering any array formula it shows a pair of curly braces around the formula. Excel gives it automatically. You don’t need to do it manually*.

- Lastly, use
**Fill Handle**to do the same to the remaining cells.

Similarly, you can use the formula for the numbers as well. In the below image, we have shown you the result of using this formula for the **Salary** column.

`=SUM(--EXACT($D$7:$D$23,F16))`

**Read More:** **Excel VBA to Count Duplicates in a Column (A Complete Analysis)**

**Similar Readings**

**How to Count Duplicate Rows in Excel (4 Methods)****Count Duplicate Values Only Once in Excel (3 Ways)****How to Count Occurrences Per Day in Excel (4 Quick Ways)**

### 3. Inserting COUNT and IF Functions

We have seen how to calculate the number of occurrences using the **COUNTIF function**. This time we’ll see the use of **COUNT** and **IF** functions.

Don’t get confused, while in the **COUNTIF** section there we used a single function (**COUNTIF**) but in this section, we will use **COUNT** & **IF** two separate functions.

**📌**** Steps:**

- At the very beginning, write the
**IF function**of this formula for the**Sales Rep**column.

`=IF($B$7:$B$23=F7,$D$7:$D$23)`

- Then, press
**ENTER**.

The array provides the corresponding value from the number range which matches the criteria and **FALSE** for others.

Here it found **4** matches so in those 4 places, they returned the ** number range** value (

**Salary**).

- Now inside the
**COUNT function**, we’ll count these number values and will provide the number of occurrences.

`=COUNT(IF($B$7:$B$23=F7,$D$7:$D$23))`

- Similarly, you can do this for the number values. Just replace the fields with the appropriate ranges and criteria.

`=COUNT(IF($D$7:$D$23=F16,$D$7:$D$23))`

*Note:** Make sure your second parameter within the IF function is a number range and you are using Absolute Reference*.

**Read More:** **VBA to Count Duplicates in Range in Excel (4 Methods)**

### 4. Applying SUM and IF Functions

Within the **IF function,** we are checking whether the criteria have matched or not, if it matches then it returns **1**, otherwise **0**. This gives an array of **1** and **0** to the **SUM function** and then it sums up the array and provides the answer. Let’s see the detailed process below.

**📌**** Steps:**

- Initially, go to cell
**G7**and inset the formula below.

`=SUM(IF($B$7:$B$23=F7,1,0))`

- Then, tap the
**ENTER**key.

The formula will work fine for the number values as well.

`=SUM(IF($D$7:$D$23=F16,1,0))`

**Read More:** **How to Count Duplicates in Column in Excel (3 Ways)**

### 5. Employing PivotTable

You can use the **PivotTable** for counting the number of occurrences for each value within the column. Let’s see the process in detail.

**📌**** Steps:**

- At first, select any cell inside the range. Here, we selected cell
**B4**. - Secondly, go to the
**Insert**tab. - Thirdly, click on
**PivotTable**on the**Tables**group.

The **PivotTable from table or range** dialog box will open upon you.

- Here, check the
**Table/Range**is correct or not. - Accordingly, select
**Exiting Worksheet**as we want to insert the**PivotTable**in the same sheet. - Then, give the
**Location**. Here, we did it as cell**F4**. - Following this, click
**OK**.

The pivot table will appear on you like the image below.

Here inside the **PivotTable Fields** task pane, you will see the table’s column name in the **Field** section. And four areas: **Filters**, **Columns**,** Rows**,** Values**.

- Presently, drag the
**Sales Rep**field into the**Rows**and**Values**area.

It counts the occurrence number of each value within the **Sales Rep** column.

**Read More: ****Count Duplicates in Excel Pivot Table (2 Easy Ways)**

## Counting Number of Occurrences with Multiple Criteria in Excel

In the previous sections, we learned to count the number of occurrences of each value in a column. Here, we’ll show how we can count the number of occurrences with multiple criteria.

Here, we’ll show the demo for Max and John. From the dataset, we can see that there are **Max** in **New York**, **Los Angeles,** and **San Fransisco**. But we want to count **Max** just from **New York** city. To do this,

- Primarily, go to cell
**H5**and paste the following formula into the cell.

`=COUNTIFS($B$5:$B$21,F5,$C$5:$C$21,G5)`

Here, we used the **COUNTIFS function** which is able to take multiple criteria.

- Then, press
**ENTER**.

**Read More: ****How to Count Duplicates Based on Multiple Criteria in Excel**

## Conclusion

That’s all for today. This article explains how to count the number of occurrences of each value in a column in Excel in a simple and concise manner. Don’t forget to download the Practice file. Thank you for reading this article. We hope this was helpful. Please let us know in the comment section if you have any queries or suggestions. Please visit our website, **ExcelDemy**, a one-stop Excel solution provider, to explore more.