Getting unique count in a pivot table in excel

Question

Here is the data source from which I am trying to create a pivot table, but failed. Looking forward to some expert to help me out:

DATA SOURCE:

ticket number   agent   service name
-------------------------------------
123             agent1    service1
123             agent1    service1
123             agent1    service1
123             agent1    service2
123             agent1    service2
234             agent3    service8
234             agent3    service8
234             agent3    service8
234             agent3    service8
234             agent3    service6
234             agent3    service6
234             agent3    service4
234             agent3    service4
345             agent6    service7
345             agent6    service7
345             agent6    service7
345             agent6    service7
345             agent6    service7
345             agent6    service7
345             agent6    service7
345             agent6    service1
345             agent6    service2
345             agent6    service1
345             agent6    service1
345             agent6    service4
345             agent6    service4

Here is the pivot table I want from the source. The number under each agent is the UNIQUE TICKET COUNT:

service name    agent1  agent2  agent3  agent4
-----------------------------------------------
service1          3         
service2                  1     
service3                  2        1        
service4                
service5          1                2        
service6                  2     
service7                                   4            

Answer 1

You can create another column in source data with formula:

=IF(SUMPRODUCT(($A$2:$A2=A2)*($B$2:$B2=B2)*($C$2:$C2=C2))>1,0,1)

Then just use sum of that column in pivot table as it will output the distinct count.