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 )

SUM(1/COUNTIF(tbl[[Product ]],tbl[[Product ]]))

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

=COUNTA(UNIQUE(item_list))

Count Unique in Excel

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

=SUM(IF(COUNTIF(tbl[[Product ]],tbl[[Product ]])=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

Count Duplicates in Excel

=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!

Print Friendly, PDF & Email

Do you want to excel in Excel?

Or

Seeking Microsoft Certification?

Every Week Receive Resources To Get you Closer to Your Goal!

No Huff! No Fluff! No Spam!

You are on your way to Excelling in Excel and Becoming Microsoft Certified!

Share This

Spread the Good News!

Do me favour, please? Share this with your friends!

Thanks For Commenting!

Awesome for you to comment! Please share the post with your friends.