EXCEL 2013: Dynamic/Unique value search from pivot

 

 

Hello, 

So I’m trying to built a formula from a pivot, so I can then enter it in a macro and prevent manual work. The problem I’m having is in creating the correct formula from the pivot. The reason for that is that the data in the pivot, varies every month, AND I need to do it by unique values, in this case agent name.

Below I enter an example of the pivot, the 2 examples of formulas and the outcome I need. 

I need to get a list of all agents in the pivot with the count of contacts with ‘0’ and ‘1’, to be divided by the total count of contacts, in a percentage. 

Pivot example: 

Row Labels Count of Contacts
Agent 1 1
1 1
Agent 2 2
1 2
Agent 3 2
0 1
3 1
Agent 4 6
0 1
1 2
5 3
Agent 5 5
0 1
1 4
Agent 6 3
1 2
2 1
Grand Total 19

Formula 1 example:

=GETPIVOTDATA(“Contacts”,$A$3,”Assigned Account”,”Agent 4″)-(SUM(GETPIVOTDATA(“Contacts”,$A$3,”Assigned Account”,”Agent 4″,”Contacts”,0)+GETPIVOTDATA(“Contacts”,$A$3,”Assigned Account”,”Agent 4″,”Contacts”,1)))

Outcome here is 3 (1 time ‘0’ + 2 times ‘1’) BUT manually needed to put “agent 4” here

Formula 2 example:

=(GETPIVOTDATA(“Contacts”,$A$3,”Assigned Account”,”Agent 4″)-E4)/GETPIVOTDATA(“Contacts”,$A$3,”Assigned Account”,”Agent 4″)

Outcome here is 50%. The ‘3 from the previous formula divided by the total count for agent 4, which is ‘6’. ALSO manually made this for agent 4

Outcome need example: 

Agent 1 100%
Agent 2 100%
Agent 3 50%
Agent 4 50%
Agent 5 50%
Agent 6 67%

Hope this is clear and someone can help me out. 

thanks for trying 🙂 

Regards

Kevin 

 

By: Kevin

Leave a Reply

Your email address will not be published. Required fields are marked *