How to count the number of Distinct/Unique items in Excel?


While working with Excel big data, we may look for solutions to find out the distinct/unique number of items in a specific column. To do this, most of us just do it by removing duplicates. The following formula will help us to do it easily
=SUMPRODUCT(1/COUNTIF(A2:A49,A2:A49))

Let's break down how this formula works:

COUNTIF Function: The COUNTIF function is used to count the occurrences of each item in the range A2:A49.

1/COUNTIF: By taking the reciprocal (1/x) of the counts, we essentially obtain an array of fractions where each fraction represents the uniqueness of the corresponding item. Items that appear only once will have a fraction of 1, and those that appear more frequently will have a smaller fraction.

SUMPRODUCT: Finally, SUMPRODUCT is used to sum up these fractions. The result is the total count of unique items in the specified range.

This formula is a powerful tool for data analysis, especially when dealing with large datasets. It provides a concise and efficient way to determine the number of distinct items, aiding in various statistical and analytical tasks.

Note: Adjust the range A2:A49 based on the actual range of your data or you can make a dynamic range. To learn about the dynamic range click here.