Get count based on column value in Excel

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.

Excel Count Number of Occurrences of Each Value in a Column

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

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.

Further Readings