If you have a list of data with duplicate values, you need to count the duplicate values. In this case, how can you calculate them quickly? For that, Excel provides the COUNTIFS function. With its help, we can get the count of specific values in the range. Today, we will guide you to learn the Formulas to count the values that appear in a List or Table in Excel. Get an official version of MS Excel from the following link:
https://www.microsoft.com/en-in/microsoft-365/excel
Jump To
General Formula
Here we come up with a simple formula to find the total count of values that appear in a list in Excel.
=COUNTIFS(B:B, B5)
Syntax Explanations
- COUNTIFS – The Excel COUNTIFS Function will help to count the number of cells that match multiple criteria or conditions.
- Range (A: A, B: B) – It represents the input ranges given in a worksheet.
- Criteria (A1, B1)- It is the condition that helps to count the cells.
- Comma symbol (,) – It is a separator that helps to separate a list of values.
- Parenthesis () – The main purpose of this symbol is to group the elements.
How to Apply Formula to Count Items in a List?
In order to count the values in the range, we can use an Excel formula. This formula can make use of the COUNTIFS function for finding the total count of values that appears in the list repeatedly. For instance, we will need to find how many times a specific value appeared in the list.
The following image will show you a list of fruits and count the values in the range.
After that, you can use the below-mentioned formula in any cell where you want to get the output result.
=COUNTIFS(B:B,B5)
Once you have entered the formula, click on the Enter button to execute it and get the result. After getting the result for the first cell, you just need to drag down the cell to fill the remaining cells in the range.
If the above-given scenario is not enough to understand this formula and want to learn this with a simple example, kindly move to the following section.
Alternate Example
Here we come up with a simple example to know how to apply this Excel formula in the spreadsheet.
- In the below example image, you can see the list of student names in the range. And we want to get the count of those students’ names from the list.
- Then, enter the given formula in a cell where you want to get the result.
=COUNTIFS(B:B,B5)
- Finally, you will need to click the Enter button to get the result, as shown in the below screenshot.
Wind-Up
Here, you can learn the Formulas used for counting the values that appear in a List or Table in Excel with brief explanations. If you have any doubt regarding this article or have any other questions related to Excel, let me know in the comments section below. To learn more, check out AawExcel. Read more on Excel Formulas.
Video Tutorial
The following video will show you how to use an Excel formula to count values that appear in the range in Excel.
Read Ahead:
- How to Use Excel FLOOR.MATH Function?
- How to Use Excel FLOOR Function?
- How to Use Excel CEILING.PRECISE Function?
- How to Use Excel CEILING Function?
- How to Use Excel GCD Function?
Deepak is an Excel enthusiast and data analyst with over 5 years of experience in the field. I’m a renowned author for AAW Excel who connects with a committed group of Excel users by imparting his professional knowledge and helpful advice. No matter your level of Excel proficiency, my articles and tutorials will teach you something new and beneficial. Connect me via my social links!… Mastodon & More!
