Count Unique & Distinct Values in Microsoft Excel (With Examples)

Unique and distinct values are fundamental in data analysis. Here's how to easily count them in Excel.

1. Basic COUNTIF Function: The COUNTIF function can be used to count unique values in a range. It counts the number of cells within a range that meet the given condition.

2. Understanding COUNTIF

3. COUNTIFS for multiple conditions: The COUNTIFS function counts the number of cells within a range that meet multiple criteria. This can be used for counting unique values across multiple columns.

4. Mastering COUNTIFS

5. FREQUENCY Function: The FREQUENCY function counts the number of values that fall into each of several bins. This function can be used to count unique numeric values in a range.

6. Using FREQUENCY

7. Using Pivot Tables: Pivot Tables can quickly count unique values by using the "Distinct Count" summary function.

8. Pivot Table Distinct Count

9. SUMPRODUCT and COUNTIF combination: For larger datasets, a combination of SUMPRODUCT and COUNTIF can provide faster calculations.

10. SUMPRODUCT and COUNTIF

11. Using Power Query: For more complex scenarios, Power Query is a powerful tool that can be used for counting unique values.

12. Counting unique values in Power Query

In Excel, there's always more than one way to skin a cat. Here, we explore six different methods for counting unique and distinct values in your datasets. By mastering these techniques, you can start to harness the true power of Excel.