Tip Printed from ExcelTip.com

---

Counting Unique Numeric Values or Unique Data in a List


Problem:

Counting the number of unique numeric values or unique data in List1, disregarding blank cells.

Solution1:

To count the number of unique values use the SUM, IF, and FREQUENCY functions as shown in the following formula:
= SUM(IF(FREQUENCY(A2:A13,A2:A13)>0,1))

Solution 2:

To count the number of unique data use the SUMPRODUCT and COUNTIF functions as shown the following formula:
=SUMPRODUCT((A2:A13<>"")/COUNTIF(A2:A13,A2:A13&""))