Share on twitter
Share on linkedin
Share on facebook
Share on pinterest
Share on email
Share on print
Share on twitter
Share on linkedin
Share on facebook
Share on pinterest
Share on email
Share on print

Count Unique Text with Excel Pivot Table

In this Article, you will find a tip to count Unique Text using Pivot Tables of a specific category in Row or Column.

It is easy to count text or number in Excel, but when you want to count unique texts with a condition, you may find a complicated formula like this:

{=SUM(N(FREQUENCY(IF(B4:B14=F4,MATCH(C4:C14,C4:C14,0)),ROW(C4:C14)-ROW(B3)>0))}

However, there is an simple option with Pivot Tables where you can easily find the unique count of texts. Let’s understand this with an example.

  • While writing this I used Office 365, however, anyone using Excel 2016, Excel 2013 and even prior editions of Excel should be able to follow along with the content.

Download Your Example Excel File

We have two columns Product Manager (PM) and Vendor Name (VN). Our task is to find the unique count of VN who bought from a PM irrespective of number times they purchased. The purpose is to calculate how many vendors a PM handle using Pivot tables.

Convert list into pivot table as part of data model
Convert list into Pivot Table as part of Data Model

We can create a Pivot table using Icon highlighted in Image from the list and make sure you tick mark the checkbox (Add this data to the Data Model).

Count using Pivot Table and Select Value Field Settings
Count Vendor Name using Pivot Table and Select Value Field Settings

If you notice the ‘Count of Vendor Name’ these numbers are not Unique Names.

Rows – Product Manager
Values – Vendor Name

Like the count adjacent to Moss is 4 but ‘Reimers’ is Twice in the list. However, our task is to find Unique value.

Now Returns the count of only Unique Category
Now Returns the count of only Unique Vendor name

You can select Distinct Count option in the Value Field Settings pop up.

The result will show Unique count of vendors. Like vendors count of Moss is 3 (unique) not 4 as above.

This helpful tip can make things very simple to count unique value in Excel.