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.
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.
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).
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.
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.
