To count the unique values (don't be overwhelmed), we add the SUM function, 1/, and replace 5 with A1:A6. Explanation. By setting this parameter to false the UDF will exclude the blank rows. ‘count_blanks’ – is a boolean parameter that can have two values true or false. We use the COUNTIF function. 2. To count unique text values in a range, you can use a formula that uses several functions: FREQUENCY, MATCH, ROW and SUMPRODUCT.In the example shown, the formula in F5 is: = SUMPRODUCT ( -- ( FREQUENCY ( MATCH ( B5:B14 , B5:B14 , 0 ), ROW ( B5:B14 ) - ROW ( B5 ) + 1 ) > 0 )) The formula in F5 is: = SUM( -- (FREQUENCY( B5:B14, B5:B14) > 0)) which returns 4, since there are 4 unique employee ids in the list. Looking from the inside out, here's what each function does: The COUNTIF function counts how many times each individual value appears in the specified range. For example, to count the number of 5's, use the following function. On this first worksheet, this list of colors contains duplicates. To count unique numeric values in a range, you can use a formula based on the FREQUENCY and SUM functions. Finish by pressing CTRL + SHIFT + ENTER. Now that dynamic array formulas, and especially the UNIQUE function, are part of Excel, this is all much easier. Before dynamic array formulas, counting unique values in Excel involved complex array formulas, especially if you needed to count values based on one or more conditions. If you set this parameter as true then it will count the blank rows as unique.

The IF function evaluates each value in the array returned by COUNTIF, keeps …
Count Unique Values 1. In the example shown, employee numbers appear in the range B5:B14. 3. So, these were all the methods that can help you to count unique value in excel.