To count unique numeric values in a range, you can use a formula based on the COUNTA, UNIQUE, FILTER and ISNUMBER functions.
How to count unique numeric values in a range?
Here are the steps to count unique numeric values in a range in Excel:
- Select cell F3.
- Type =COUNTA(UNIQUE(FILTER(A1:A10, ISNUMBER(A1:A10))))
- Press Enter.
- The formula will return the count of unique numeric values within the specified range.
Example
In this example, we have a single dataset in column “Range,” which contains a series of numeric values, some repeated. The goal is to count the unique numeric values in this range. The formula in cell F3 filters the numeric values in the range, removes duplicates using the UNIQUE function, and counts the remaining unique entries.
Formula:
=COUNTA(UNIQUE(FILTER(C3:C12, ISNUMBER(C3:C12))))
The result in cell F3 represents the total number of distinct numeric values found in the dataset.
Explanation
Let’s break down how the formula =COUNTA(UNIQUE(FILTER(C3:C12, ISNUMBER(C3:C12)))) works.
ISNUMBER(C3):
The ISNUMBER function checks whether each cell in the range C3:C12 contains a numeric value. The formula returns an array of TRUE and FALSE values, where TRUE indicates that a cell contains a number, and FALSE indicates it does not. For example, if C3:C12 contains numbers and blanks, ISNUMBER will return TRUE for numbers and FALSE for blanks or text.
FILTER(C3, ISNUMBER(C3)):
The FILTER function takes two arguments: C3:C12: This is the range to filter. ISNUMBER(C3:C12): This array of TRUE/FALSE values acts as a condition. FILTER includes only the values in C3:C12 where the condition (ISNUMBER(C3:C12)) is TRUE, filtering out any non-numeric values. The result is an array of only the numeric values from C3:C12.
UNIQUE(FILTER(C3:C12,ISNUMBER(C3:C12)))
UNIQUE takes the filtered array of numeric values and removes any duplicates, leaving only distinct numeric values. For example, if C3:C12 contains [123, 123, 44, 543, 531, 4555, 223, 123, 444, 44], after filtering, UNIQUE return [123, 44, 543, 531, 4555, 223, 444].
COUNTA(UNIQUE(FILTER(C3:C12,ISNUMBER(C3:C12))):
COUNTA counts the number of items in the array returned by UNIQUE. Since UNIQUE provides only distinct numeric values, COUNTA effectively counts the unique numeric values in C3:C12.
Using SUMPRODUCT
Let us see the workaround with the SUMPRODUCT function.
Formula:
=SUMPRODUCT(1/COUNTIF(C3:C12, C3:C12)*(ISNUMBER(C3:C12)))
Explanation:
COUNTIF(C3:C12, C3:C12):
COUNTIF here is used in an array-like manner, where C3:C12 is both the range to evaluate and the criteria. This setup generates an array in which each element represents the count of the corresponding value in C3:C12 within the range C3:C12. For example, if the number 123 appears three times, COUNTIF will return 3 for each instance of 123.
1/COUNTIF(C3:C12, C3:C12):
Dividing 1 by each element of the COUNTIF array effectively creates fractions. If a value appears once, it results in 1/1 = 1. If it appears twice, it results in 1/2 = 0.5, and so on. This transformation helps to ensure that each unique value contributes exactly 1 to the sum, regardless of duplicates. For example, three occurrences of 123 would contribute 1/3 + 1/3 + 1/3 = 1 to the total.
ISNUMBER(C3:C12):
ISNUMBER returns an array of TRUE (1) and FALSE (0) values, with TRUE for numeric entries and FALSE for any non-numeric values. Multiplying this array with the 1/COUNTIF array ensures that only numeric values are counted, as non-numeric values will yield 0 and not contribute to the total.
SUMPRODUCT(…):
SUMPRODUCT multiplies corresponding elements in the arrays generated by 1/COUNTIF and ISNUMBER, then sums the results. This final sum represents the count of unique numeric values in the range C3:C12.