When you want to excel count of values, you may use the COUNT function and its variants. But what happens if you simply want to count the unique values inside a certain cell range? This lesson will teach you two different approaches to counting unique numbers in Excel.
Both of the approaches that we are going to describe using several other functions and many variants of the COUNT function. In addition, since the range of cells you’re working with can include empty cells, we have provided many different formulae to accommodate this possibility.
This first approach is for individuals who use Excel for Microsoft 365, Excel for the web, Excel 2021, or Excel for iPhone, iPad, or Android phones or tablets. Those who use Excel for the web may also utilize Excel 2021. This is because the UNIQUE function can only be accessed in these versions of Excel and any subsequent versions.
Excel Count Of Values Using Unique And Counta
The UNIQUE function returns all of the unique values included inside a given cell range. You’ll need to add the COUNTA function since you don’t want to show those values only found in one place but rather count them. The COUNTA function tallies the number of cells that are not blank.
Method 1: You would use this formula to list the one-of-a-kind values included in the range of cells A2 to A5: “=UNIQUE(B1:B8)” You just have to put the formula in a cell and apply the formula. This formula will list the unique values for you.
Method 2: You may count those unique values without listing them by appending the COUNTA function to the front of the calculation, which looks like this: “=COUNTA(UNIQUE(B1:B8))” Apply the formula, and you will get the number of excel count of values.
Excel Count Of Values Using Sum And Countif
You may use SUM and COUNTIF instead of the UNIQUE and FILTER functions in Excel if you are working with a program version that does not provide such capabilities.
Method 1: The COUNTIF function counts cells that contain numbers that fulfill particular requirements, whereas the SUM function sums the numbers included in the cell.
Use the following formula to locate the singular values that are included within the range of cells A2 to A5: “=SUM(1/COUNTIF(B1:B8,B1:B8))”
The COUNTIF function counts the cells that contain numbers that fall inside our range and then utilize that same cell range as the criterion to deconstruct this formula. Finally, this result is multiplied by 1, and the SUM function is used to sum the leftover numbers.
Method 2: Like the first way, using this strategy might provide challenges for you if your cell range contains blanks. However, rather than an unique added value, you will obtain the #DIV/0! Error.
You may solve this issue by using the following mathematical formula:
“=SUM((B1:B8<>””)/COUNTIF(B1:B8,B1:B8&””))”
Because you cannot divide by zero, the extra component of the COUNTIF function must concatenate a blank string to avoid the appearance of zeroes in the results. The numbers that do not equally blank are tacked on by the SUM function’s supplementary part, which adds them up. The total of all of these things gives the count of unique values. However, it does not count blanks as a unique value.
Note: Youcan also use the sumproduct function instead of sum function here.
Excel Count Of Values Using Countif
We can determine the total number of occurrences of each value inside a column or range by using the COUNTIF function. The COUNTIF function tallies the number of cells inside a certain range and evaluates them according to a given criterion. The following is the COUNTIF syntax, sometimes known as the general formula.
The range of cells whose total count you want to get.
Criterion refers to the standards determining which kind of cells should be counted. You must enter your preference where. We used the COUNTIF function to enter all of the possible values for the Amount field as a range.
Every Amount was considered for our criterion, given that we need to determine the total number of occurrences for every Amount. As a result, we have included an Amount as one of the criteria.
It provided the total number of times that the Amount was used. Because our data collection is not a very large, you can quickly examine it and see that there are 2 instances of 15000 used in the data.
The formula Used will be “=COUNTIF(B1:B8, B1)”
Step 1: Insert the data into an Excel spreadsheet, take a cell, enter the formula, and check if the range and criteria are correct.
Step 1: Insert the data into an Excel Spreadsheet and take a cell and enter the formula and check if the range and criteria is correct.
Step 2: Press enter to see the results and drag the autofill handle downwards to apply the formula for the rest of the range.
Excel Count Of Values Sum And Exact
By using the SUM and EXACT functions, we can also determine the total number of occurrences for each value. The SUM function will supply you with the total for the range you provide inside it, as its name suggests (and its function description also implies). The EXACT function checks whether two numbers are identical to one another and returns TRUE if they are; otherwise, it returns FALSE. This function is often used for text value applications. Likewise, the formula may also be used for the integers if you so want. For example, you can see the result you get when using this formula for the Salary column in the picture below. The formula used for this method will be “=SUM(–EXACT($A$1:$A$8,E4))” Here, E4 is the query and the rest is the range.
Step 1: Open the previously used sheet and enter the names in a separate cell and take an adjoining cell and enter the formula.
Step 2: Check the range of the formula to see if they are correct, and press enter to see the results.
Conclusion
That brings excel count of values to a close. We have made an effort to offer a variety of methods that may be used to count excel count of values inside a column. I hope that this will come handy and will save some time for you. If there is anything that appears unclear to you, please feel free to remark on it. Please let us know which approaches you have found to be the most helpful and which you intend to employ. You are more than welcome to inform us of any more techniques we may have overlooked here.