Hello! I've been trying to update a spreadsheet for work. I'm unable to post too much data for privacy reasons, but I'm still hoping I can get some help. Right now my job invloves a grading scale using flags, an agent can be flagged for multiple times per call for different reasons. I'm looking for a way to count up how many times a specific agent has been flagged total per call based on what is noted in cell I:I.
These are the two formula's I was playing with. A9 represents the agents name while O2 represents the phrase "Yellow Flag". Currently the first formula shows that this particular agent was noted to have 9 calls where they were yellow flagged. Counting them up individually I know they were actually noted to have 26 total yellow flags over these 9 calls. The second formula Shows the total ammount of yellow flags over all of my agents, which in this case is 137. So my question is, how can I get the total number of times agent A9 was noted for a yellow flag (in this case 26 instead of 9)? I'd like to be able to apply this to every agent to quickly reference how many they have.
In the first formula H:H is the agent names, and I:I is the notes.
=COUNTIFS(Data!$H:$H,Breakdown!A9,Data!$I:$I,"*Yellow Flag*")
=SUMPRODUCT((LEN(Data!I:I)-LEN(SUBSTITUTE(Data!I:I,O2,"")))/LEN(O2))
I would appreciate any help I could get on this. I apologize if this wasn't explained particuarly well.
These are the two formula's I was playing with. A9 represents the agents name while O2 represents the phrase "Yellow Flag". Currently the first formula shows that this particular agent was noted to have 9 calls where they were yellow flagged. Counting them up individually I know they were actually noted to have 26 total yellow flags over these 9 calls. The second formula Shows the total ammount of yellow flags over all of my agents, which in this case is 137. So my question is, how can I get the total number of times agent A9 was noted for a yellow flag (in this case 26 instead of 9)? I'd like to be able to apply this to every agent to quickly reference how many they have.
In the first formula H:H is the agent names, and I:I is the notes.
=COUNTIFS(Data!$H:$H,Breakdown!A9,Data!$I:$I,"*Yellow Flag*")
=SUMPRODUCT((LEN(Data!I:I)-LEN(SUBSTITUTE(Data!I:I,O2,"")))/LEN(O2))
I would appreciate any help I could get on this. I apologize if this wasn't explained particuarly well.