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.
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.
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
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.
Oh! It’s providing faulty value. We made a mistake.
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.
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.
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.
Our formula using the SUM and EXACT function will be something like this.
For a better understanding of the formula, write the EXACT function portion first.
- Firstly, select cell G7 and enter the following formula.
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.
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.
- 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.
- At the very beginning, write the IF function of this formula for the Sales Rep column.
- 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.
- Similarly, you can do this for the number values. Just replace the fields with the appropriate ranges and criteria.
Note: Make sure your second parameter within the IF function is a number range and you are using Absolute Reference.
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.
- Initially, go to cell G7 and inset the formula below.
- Then, tap the ENTER key.
The formula will work fine for the number values as well.
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.
- 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.
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.
Here, we used the COUNTIFS function which is able to take multiple criteria.
- Then, press ENTER.
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.