How To Count In Excel Unique Values

The easiest way to count unique values in a column is to use the unique function together with the counta function.

How to count in excel unique values. The syntax for this combined formula is sum if 1 countif data data 1 1 0. This example shows you how to create an array formula that counts unique values. When counting unique values use the following expression. To the best of my knowledge excel still doesn t have a built in function for counting unique values. The simplest and easiest way to count distinct values in excel is to use sumproduct and countif formula.

We use the countif function. Get count of unique numbers from a list and if you just want to count unique numbers from a list of values then you can use below formula. For each occurrence of that same value after the first this function returns a zero. The countif function returns the count of values based on a criteria. If sum exact a 2 a 10 a2 1 unique dupe and then use a simple countif function to count unique values.

Count case sensitive unique values in excel. Sumproduct 1 countif b5 b14 b5 b14 with excel 365 you can use a simpler and faster formula based on unique. Using a range an array of cells and values to create a criteria makes the function calculate for all cells in the criteria range. Following is the generic formula that you can use. Count unique values in excel you can use the combination of the sum and countif functions to count unique values in excel.

Unique returns an array of unique entries and counta counts all the elements of the array. Here the countif formula counts the number of times each value in the range appears. To count the number of unique values in a range of cells you can use a formula based on the countif and sumproduct functions. Sum if isnumber a2 a17 1 countif a2 a17 a2 a17 the change below works for me in excel 2016 by deleting the double quotes at the end after the comma. Making every repeating value a fraction of its occurrence by dividing 1 by their counts.

If your table contains case sensitive data the easiest way to count unique values would be creating a helper column with the following array formula to identify duplicate and unique items. In the example show the formula in f6 is. The frequency function ignores text and zero values. To count the unique values don t be overwhelmed we add the sum function 1 and replace 5 with a1 a6. Counta unique range the formula works with this simple logic.