Trying to count how many times a specific phrase appears for multiple people

[Jclark031]

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.

 

[pjmorris]

Firstly, welcome to the forum - there are many skilled individuals here who can almost certainly provide help. But no one is clairvoyant and neither can they see your spreadsheet. The ideal question provides an example of the source data, the output you hope to achieve and possibly the output your currently getting. Ideally use the XL2BB tool (in toolbar here) to paste a sample of your spreadsheet to the board.

For your particular question you need to provide a random sample of your data in columns H, I and O - without knowing how you identify a yellow flag in each call I don't know how to answer the question.

 

[Jclark031]

Absolutely! Its pasted over from a raw data excel sheet so it just complies the notes made into one cell. I'm really just looking for a formula to count how many times yellow flag was stated in the notes for my 26 agents so I can properly report who's having the most issues.

Yellow Flag - Agent did not validate customers information. Yellow Flag - Agent did not inform the customer of the automatic renewal Yellow Flag - Agent did not mention the after promo rate Yellow Flag - Agent did not state package name Yellow Flag - Agent didn’t confirm the make and model of the vehicle

 

[pjmorris]

The formula you need to count the number of occurrences in cell K3 is =(LEN(K3)-LEN(SUBSTITUTE(UPPER(K3),"YELLOW FLAG","")))/LEN("YELLOW FLAG")

I think I'd put it in a helper column and then use SUMIFS to sum for each agent.

PS. How about counting how many times they get something right, much more positive and a nice balance to what goes wrong.

HTH

 

[Jclark031]

Haha! The ideally we get them to 0 so that we can only praise them!

 

[Jclark031]

I sincerely appreciate the help. Out of curiousity where did you get cell K3 from? To give what I hope is a better example of what i'm looking at I pasted an example below. See agent 8 in this scenario is A9 in my current formula. I can see that she was marked yellow flagged nine times. But in the notes she actually recieved a total of 26 yellow flags as she was flagged multiple times per call. I'm wondering how to straight read from the I:I data and get the 26 yellow flags number based off her name which in this example is Agent 8

This is the formula i used to come up with the 9 times =COUNTIFS(Data!$H:$H,Breakdown!A9,Data!$I:$I,"*Yellow Flag*") However this is only counting each flag once. I'd like it to count all 26.

Agent	Valid	Yellow	Orange	Red
Agent 1	10	3	0	0
Agent 2	12	3	1	0
Agent 3	18	2	0	0
Agent 4	12	2	0	0
Agent 5	6	5	0	0
Agent 6	5	2	0	0
Agent 7	0	5	1	0
Agent 8	2	9	0	1

 

[CA_Punit]

See below post

 

[CA_Punit]

But in the notes she actually recieved a total of 26 yellow flags

Where is the notes??

 

[Jclark031]

This line seems to be contradictory.
I mean you are counting how many yellow flag are there for Agent 9. It will show 9. How can it be 26. Is there other words in cells along with Yellow Flag. Since Countif will ignore those

I apologize for being difficult. I'm not sure what I can share from work without violating anyones privacy so I'm trying to be as careful as possible.

Basically I have two tabs, the first tab I have named Data and its where i'm pasting the raw data I'm getting from another excel sheet. My second tab is called Breakdown and it includes the example I posted above. The notes are located in Data!$I:$I. I've reposted the example of what the notes look like below. Essientially with the count if it only says this agent has 9 yellow flags. But there are instances like below where on this particular call there were 5 yellow flags noted. I want the formula to calculate how many times the word yellow flag appears in each cell in Data!$I:$I. If like below it appears 5, I'd like it to count as 5 instead of 1. Looking through manually I can see this specific agent has 26 yellow flags across their 11 calls, but the count if formula only shows it as 9.

Yellow Flag - Agent did not validate customers information. Yellow Flag - Agent did not inform the customer of the automatic renewal Yellow Flag - Agent did not mention the after promo rate Yellow Flag - Agent did not state package name Yellow Flag - Agent didn’t confirm the make and model of the vehicle

 

[CA_Punit]

So from what i understood i need details of Data Sheet. Is there any reference to Agent name in Data Sheet.