For performing different tasks in Excel you may need to count the occurrence number of each value. Agenda for this article is to show you how to count the number of occurrences of each value in a column.

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

Here we have a table of three columns, *Customer name, City, Salary. *There are a few values that are repeated within the columns for making the examples understandable.

*Customer Name *and *City *are columns of text values and *Salary *for numbers values. This relation and data set are for practice purposes only.

## Practice Workbook

You are welcome to download the practice workbook from the link below.

## Count Number of Occurrences of Each Value

### 1. Using COUNTIF function

Using the **COUNTIF** function we can count the number of occurrences of each value in a column or range.

The **COUNTIF** function counts the number of cells within a range comparing a particular condition. Syntax or generic formula of **COUNTIF **is as follows

`COUNTIF(range, criteria)`

**range: **The range of cells you want to count

**criteria:** The criteria that control which cells should be counted. You need to insert your choice where.

Let’s write the formula for counting the *Customer Name *in our example Excel workbook

Within the **COUNTIF **function, we inserted all the values of *Customer Name *as *range.*

Our criteria 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 *Max. *

As our data set is not a big one, you can have a quick look and find there are 4 ‘Max’ inside that *Customer Name *column.

For the rest of the values, you can use Excel **AutoFill**.

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

We didn’t use **Absolute Reference, **so our cell references kept changing and gave wrong output.

So, we need to use **Absolute Reference **before exercising **AutoFill**. ** **

This time it provided the correct values.

But think a little bit, whether this is in a format to fetch value in 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.

Select your desired column and explore the *Data *tab. You will find the *Sort & Filter *option. There you will see the *Advanced *icon.

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

Select *Copy to another location *and insert the cell where you want to copy.

Make sure to check *Unique records only *and hit **Enter **or click **OK. **

Now all the unique values from the column have been selected to a separate location. 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.

Write the formula or use Excel **AutoFill**.

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

Write the formula for the *Salary *column of our example.

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

Write the formula for the rest of the values. You can use the Excel **AutoFill** feature as well.

### 2. Using SUM-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.

Our formula using **SUM**–**EXACT **function will be something like this

`SUM(--EXACT(range,criteria))`

For better understanding the formula write the **EXACT **function portion first

Here we have written the **EXACT** function for *Customer Name. *Now press **F9 **key.

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.

Since this is an array formula you need to use **CTRL + SHIFT + ENTER** instead of just **ENTER** to operate this formula.

Do the same for the rest of the values.

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

### 3. Using COUNT-IF functions

We have seen how to calculate the number of occurrences using the **COUNTIF** function. This time will see using **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.

Let’s see the formula first

`COUNT(IF(logic check whether the criteria within the range, number range)) `

Inside the **IF **function, there are two parameters. With the first parameter, we will check whether our criteria is in the range or not.

Our second parameter has to be a number range. If you insert a range of any other format value, it will not work.

Write the **IF **portion of this formula for the *Customer Name *column

Instead of **Enter **press **F9 **key, you will see the array that creates using **IF **function

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 that 4 places gave the number range value (*Salary). *

Now inside the **COUNT **function, these number values will be counted and will provide the number of occurrences. * *

It gave the result we wanted. Do the same for the rest of the values.

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

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

### 4. SUM-IF functions to count Number of Occurrences

Our formula using **SUM** and **IF** functions will be like something below

`SUM(IF(logic check within range, 1,0)) `

Within the **IF **function we are checking whether the criteria have matched or not, if it matches then 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. ** **

Write the formula for the *Customer Name *column.

Write the formula for the rest of the values.

The formula will work fine for the numbers value as well.

Write the formula for the rest of the values or use Excel** AutoFill**.

### 5. Pivot Table

You can use the *Pivot Table *for counting the number of occurrences for each value within the column.

Before using the *Pivot Table *you need to make your table is *Format as Table. *To do so, select all the entire table and explore the Home tab, you will find *Format as Table *option.

Select any of the style formats you prefer.

One dialog box will come in front of you. Checking the range click **OK **but make sure to check *My table has headers. *

Your table will be formatted as a table. Now select the table and explore *Insert *tab, there will be an option called *Tables, *within it you will find the Pivot* Table *option.

A dialog box will open upon you. Before clicking **OK **check if the table range is correct or not.

It’s better to place the pivot table in a new sheet.

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

Here inside the *PivotTable Fields *you will see the table’s column name. And four fields: **Filters, Columns, Rows, Values. **

Let’s drag the *Customer Name *into *Rows. *You will see the unique values from the column.

Now again drag the *Customer Name *into the *Values *field.

It counts the occurrence number of each value within the column.

Same for the number values

## Conclusion

That’s all for today. We have tried listing several ways to count the number of occurrences of each value in a column. Hope you will find this helpful. Feel free to comment if anything seems difficult to understand. Write to us which of the methods you have liked most and are going to use. You are welcome to let us know other methods that we might have missed here.