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

Get FREE Advanced Excel Exercises with Solutions!

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.


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

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.

excel count number of occurrences of each value in a column

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)

Using COUNTIF Function

Within the COUNTIF function, we inserted all the values of Sales Rep 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 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.

Using Fill Handle to Count Number of Occurrences of Each Value in a Column in Excel

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

Faulty Values

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)

Using Absolute Reference to count number of occurrences of each value in a column

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.

Using Sort & Filter Option to count number of occurrences of each value

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.

working on the Advanced Filter dialog box

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

Unique values from Sales Rep Column

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

Using COUNTIF Function to count number of occurrences of each value in a column in Excel

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 Duplicates in Column in Excel


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.

Utilizing SUM and EXACT Functions

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

Using Fill Handle to 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 of using this formula for the Salary column.

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

Utilizing SUM and EXACT Functions to count number of occurrences of each value in a column in Excel

Read More: How to Count Duplicates in Two Columns in Excel


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.

Inserting COUNT and IF Functions

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

Inserting COUNT and IF Functions to count number of occurrences of each value in a column in Excel

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

Read More: How to Count Duplicate Values in Multiple Columns in Excel


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.

Applying SUM and IF Functions

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

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

Applying SUM and IF Functions to count number of occurrences of each value in a column in Excel

Read More: How to Count Duplicate Rows in Excel


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.

Employing PivotTable

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.

Inserting PivotTable

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

PivotTable Appeared on the same worksheet

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.

Employing PivotTable to count number of occurrences of each value in a column

Read More: Count the Order of Occurrence of Duplicates in Excel


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.

Counting Number of Occurrences with Multiple Criteria in Excel


Download Practice Workbook

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


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.


Further Readings


<< Go Back to Count Duplicates in Excel | Duplicates in Excel | Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Shakil Ahmed
Shakil Ahmed

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

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo