# Representative Ranked by CSAT

#### indiemusicboy

##### New Member
I'm having a rough time with this one. Buckle up: I need to create a ranking of our support team based on their aggregated customer satisfaction (CSAT) score from helping customers based on data we collect from surveys. The important data we collect from a survey - for this exercise - is as follows:
• Taken On (Survey completion Date)
• String Value (3 questions)
• Metric#1: choice of A (Excellent), B (Neutral), or C (Needs Improvement)
• Metric#2: choice of A (Excellent), B (Neutral), or C (Needs Improvement)
• Metric#3: Yes/No
• Agent (Who served the client)
What I need to calculate is:

1. CSAT per survey: calculated by taking the the number of positive answers divided by the number of questions. Negative and Neutral are not needed in this equation to find CSAT.
2. Agent average CSAT in last 30 days: Each agent name and their average CSAT score
3. Agent average CSAT in last 90 days: Each agent name and their average CSAT score

Still with me?

The data from the survey will look like this in a separate worksheet:
 A B C D E F G H I J K 1 Instance ID Taken on Assigned to Company Category Metric # Metric Value String Value Group Agent 2 AINST0042251 INC0713222 4/21/20 Bob Smith Acme Customer Satisfaction Survey 1 Quality of Service? A - Excellent Systems Jane Doe 3 AINST0042251 INC0713222 4/21/20 Bob Smith Acme Customer Satisfaction SurveyCustomer Satisfaction Survey 2 Support engineer? A - Excellent Systems Jane Doe 4 AINST0042251 INC0713222 4/21/20 Bob Smith Acme Customer Satisfaction Survey 3 Resolved to my satisfaction? Yes Systems Jane Doe 6 AINST0013764 INC0231676 7/3/17 Peter Pan Unreal Co Customer Satisfaction Survey 1 Quality of Service? C - Needs Improvement UC Team Rick Ross 7 AINST0013764 INC0231676 7/3/17 Peter Pan Unreal Co Customer Satisfaction Survey 2 Support engineer? C - Needs Improvement UC Team Rick Ross 8 AINST0013764 INC0231676 7/3/17 Peter Pan Unreal Co Customer Satisfaction Survey 3 Resolved to my satisfaction? No UC Team Rick Ross

And I want it to populate like this on a separate worksheet where it ranks the top performers (say 15 of the 50 we have):

How do I get there???????? I'm guessing it is a series of COUNTIFS with DATE ... ="&TODAY()-30 ... dependencies but not sure past that. Especially with tying it to Agent names when it could change one day to the next with new agents.

I hope this is enough info. Any help is SUPER appreciated.

### Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.

#### samitnair

##### Board Regular
Hello

Below formula will give you the count and they you can average it to get the CSAT%. I didnt understand how we reached the number 98 & 91 in the CSAT by CMC representative.

VBA Code:
``````=SUM(COUNTIFS(\$D\$2:\$D\$11,A15,\$I\$2:\$I\$11,{"A - Excellent","Yes"}))

=SUM(COUNTIFS(\$D\$2:\$D\$11,A15,\$I\$2:\$I\$11,{"A - Excellent","Yes"},C2:C11,">="&TODAY()-90))``````

#### Attachments

• 1587848699658.png
10.8 KB · Views: 2

#### indiemusicboy

##### New Member
@samitnair thank you. Great start! However I think this may require some more explaining on my part. Your codes make sense, but only if the name in column A is manually populated by me and static.

I'd like it to automatically rank the names based on their CSAT score in tandem. From highest to lowest. As well as tie in their 90day CSAT for those people.

SO I think we have to have a code for column A and C that associates to column B. And then have that table (A,B and C) rank based on Column B from highest to lowest.

What do you think? And how do I do that???

#### indiemusicboy

##### New Member
Anyone have any ideas? Need a ranking system based on their score calculated from Metric# 1 and 2 in Column G.

For the ranking table; Column A will have their name, B their current 30d score, C their 90d score - all pulled from the example data spreadsheet I provided.

Replies
3
Views
42
Replies
5
Views
3K

1,130,170
Messages
5,640,577
Members
417,151
Latest member
ChickenTenderer

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back