Require unique count in pivot or using formula

New Contributor

Hi all,


Need a quick help in figuring out the count of unique customers served by a broker.The data I have is across category and hence its possible that one broker is serving the same customer across multiple categories and hence the count in pivot is not unique.I am attaching a sample input and output data.Please suggest how can this be achieved.Thanks!


Broker NameCustomer NameCategory
Broker 1Customer 1Category 1
Broker 1Customer 1Category 2
Broker 1Customer 1Category 3
Broker 1Customer 1Category 4
Broker 2Customer 2Category 1
Broker 3Customer 1Category 1
Broker 3Customer 3Category 2


Desired OUTPUT using formula or pivot


Broker NameCount of unique customer serviced
Broker 11
Broker 21
Broker 32
3 Replies
Desired output is below
Broker Name>>Count of unique customers served
Broker 1 >1
Broker 2 >1
Broker 3>2
best response confirmed by Vaibhav002 (New Contributor)

@Vaibhav002 Use Power Pivot where you can specify a Distinct Count.



Thanks @Riny_van_Eekelen..This really helped