How to Count Distinct, Unique and Duplicates In Excel

The first step in counting distinct, unique, and Duplicates is knowing the difference between distinct and Unique.

⭐ Distinct refers to a count of items in a list after removing duplicates

⭐ Unique refers to a count of items in a list that appears ONLY ONCE.

Distinct Count in Excel

=SUM(1/COUNTIF( items_list , items_list )

If you are using Office365, try the COUNTA & UNIQUE function below

=COUNTA(UNIQUE(item_list))

=IF(COUNTIF( items_list , items_list )=1,1,0)

If you are using Office365, try the COUNTA & UNIQUE function below

=COUNTA(UNIQUE(item_list),,TRUE))

The only difference with the count distinct formula above is UNIQUE(item_list),,TRUE) returns only Unique values, not distinct ones

=SUM(
IF(
COUNTIF(items_list,items_list)>1,
1/COUNTIF(items_list,items_list),0
)
)

If you are using Office365, you will have to use a combination of COUNTA, UNIQUE, FILTER & COUNTIF

=COUNTA(UNIQUE(FILTER(item_list,COUNTIF(item_list,item_list)>1)))

How the formula Works:

COUNTIF( item_list , item_list )>1 returns an array of TRUE for all duplicated items else FALSE.

FILTER(item_list,COUNTIF(item_list,item_list)>1)) Filter returns all the duplicated items

UNIQUE(FILTER(item_list,COUNTIF(item_list,item_list)>1))) returns a distinct list of duplicated items

COUNTA returns a count of the above distinct list of duplicated items

DOWNLOAD WORKSHEET and try the same for suppliers!