Need help with formula - sorting by user

1

Hi Codans!

I started with coda a couple days ago and I love it so far. I need help with a formula. I’m building a mini crm for our call agents (we’re a call center). I want to analyse data for each agent, to be able to track their performances.

I am looking for a formula that can sum() the data that would be pulled froma column in another sheet,

Example: an agent does x calls to contact a lead. In a table, the agents adds under the column (number of calls) how many calls he had to make in order to contact the lead.

How can I do the sum() of all calls for THIS specific agent (to know how many calls the agent made in total)?

Thank you

2

Hi @Jay_Lefebvre, Glad you are loving Coda.

Do take a look at Summary/Aggregate Views and see if that helps you. Else feel free to share a sample copy of your doc here and we can help.

3
4

Thank you for your quick response @mallika

So there are two sheets I am working on. The first one is All Leads, the second one is Call Center Agents. On the last sheet, the agents are listed and the columns display different performances and stats. These stats need to be pulled from the All Leads sheet. For example, if a lead is contacted by a specific agent, the name of that agent should appear in the Modified By column in the All Leads sheet. Then, I’d like the data to be sent in the Call Center Agents Sheet. In the All Leads sheet, if an agents adds that he made 5 calls for a specific lead, I’d like the number of calls the be summed in the appropriate row in the Call Center Agents sheet. Thank you so much for you help

5

Here is the formula you would use to get sum of all calls they made -

[List of all leads].Filter([Modified by]=thisRow.Nom).[:point_right: # of calls].Sum()

Hope you can use this to create the other formulas to fill in your columns.

1 Like
6

Thank you so much! It worked, that’s amazing :star_struck:

7

Hi @mallika

I’m sorry to bother you again, seems like I’m having trouble creating another formula. I need to calculate the % of leads that our agents closed (a closed lead is a lead for whom the stage in the sheet List of All Leads is set to “:trophy: Appointment”. If the stage isn’t set to appointment, then we consider the lead isn’t closed yet.

That % must be linked to each agent, like the other formula. The answer would appear on the Call Center Agents sheet, under the % of leads closed column.

Thank you!!

8

Try this -
[List of all leads].Filter(Stage=“:trophy:Appointment” AND [Modified by]=thisRow.Nom).Count()/[List of all leads].Filter( [Modified by]=thisRow.Nom).Count()

Also, it might be good to go into your column format and chose 2 decimal places so you get a better rounded number displayed

1 Like
9

It worked! Thank you so much :laughing: