Excel Count Number of Occurrences of Each Value in a Column

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.

Excel sheet - Excel Count Number of Occurrences of Each Value in a Column

Here we have a table of three columns, Customer name, City, Salary. There are 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 is 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)

Countif - Excel Count Number of Occurrences of Each Value in a Column

range: The range of cells you want to count

criteria: The criteria that controls 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

COUNTIF result - Excel Count Number of Occurrences of Each Value in a Column

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.

Faultry - Excel Count Number of Occurrences of Each Value in a Column

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

Absolute reference - Excel Count Number of Occurrences of Each Value in a Column

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.  

Correct COUNTIF-Excel Count Number of Occurrences of Each Value in a Column

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.

Advanced filter - Excel Count Number of Occurrences of Each Value in a Column

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

Dialog box filter - Excel Count Number of Occurrences of Each Value in a Column

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. 

Values extract - Excel Count Number of Occurrences of Each Value in a Column

Now all the unique values from the column have been selected to a separate location. Now use the previous COUNTIF formula

COUNTIF extract - Excel Count Number of Occurrences of Each Value in a Column

Now 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.

AutoFill- Excel Count Number of Occurrences of Each Value in a Column

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

Write the formula for the Salary column of our example.

Numbers - Excel Count Number of Occurrences of Each Value in a Column

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.

Autofill Numbers - Excel Count Number of Occurrences of Each Value in a Column

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

EXACT-Excel Count Number of Occurrences of Each Value in a Column

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

EXACT array - Excel Count Number of Occurrences of Each Value in a Column

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 - Excel Count Number of Occurrences of Each Value in a Column

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.

AutoFill Sum Exact - Excel Count Number of Occurrences of Each Value in a Column

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.

Sum Exact - numbers - Excel Count Number of Occurrences of Each Value in a 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))  

COUNT-IF-Excel Count Number of Occurrences of Each Value in a Column

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

IF portion - Excel Count Number of Occurrences of Each Value in a Column

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

IF array - Excel Count Number of Occurrences of Each Value in a Column

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 this number values will be counted and will provide the number of occurrences.  

COUNT-IF- Excel Count Number of Occurrences of Each Value in a Column

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

AutoFill count if - Excel Count Number of Occurrences of Each Value in a Column

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

Count - if numbers - Excel Count Number of Occurrences of Each Value in a Column

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

Autofill numbers count if - Excel Count Number of Occurrences of Each Value in a Column

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)) 

SUM-IF - Excel Count Number of Occurrences of Each Value in a Column

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.

Sum - if result - Excel Count Number of Occurrences of Each Value in a Column

Write the formula for the rest of the values.

Sum-if autofill- Excel Count Number of Occurrences of Each Value in a Column

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

Sum - if numbers - Excel Count Number of Occurrences of Each Value in a Column

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

Autofill number sum if -Excel Count Number of Occurrences of Each Value in a Column

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.

Format table - Excel Count Number of Occurrences of Each Value in a Column

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

Format table box - Excel Count Number of Occurrences of Each Value in a Column

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.

Pivot table - Excel Count Number of Occurrences of Each Value in a Column

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.

Table checklist-Excel Count Number of Occurrences of Each Value in a Column

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

Pivot table - Excel Count Number of Occurrences of Each Value in a Column

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.

Rows pivot - Excel Count Number of Occurrences of Each Value in a Column

Now again drag the Customer Name into the Values field.

Count customer - Excel Count Number of Occurrences of Each Value in a Column

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

Same for the number values

Number pivot - Excel Count Number of Occurrences of Each Value in a Column

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.

shakil

My name’s Shakil. My articles are targeted to support you in enriching knowledge regarding different features related to Microsoft Excel. I am fond of literature, hope some knowledge from them will help me providing you some engaging articles even though some weary technical terms.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo