How to Count Unique Values with UNIQUE in Excel

Count distinct customers, distinct drivers in an area and distinct customer–driver pairs with COUNTA, ROWS and UNIQUE.

You will learn to

• Count distinct values with COUNTA(UNIQUE())
• Count distinct values that meet a condition
• Count distinct combinations across two columns

How it works

To count how many different values a column contains, list them with UNIQUE and count the list: =COUNTA(UNIQUE(B2:B12)). It replaces older SUMPRODUCT(1/COUNTIF()) tricks and is easy to read. Add a condition by filtering first: =COUNTA(UNIQUE(FILTER(C2:C12,D2:D12="N1"))) counts the different drivers who delivered to area N1.

For combinations across two columns, give UNIQUE both columns and count rows rather than cells: =ROWS(UNIQUE(B2:C12)). COUNTA would count every cell, doubling the answer.

=COUNTA(UNIQUE(B2:B12))