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.

 

[Jclark031]

I'm putting together a mock sheet that I can share. I'm just taking out private information from it.

 

[CA_Punit]

Just post a sample data (only with format ) so that i can work on it. It is very difficult to understand without the data

 

[Jclark031]

Here is the Data Tab

Client Name	Product Name	Reason	Reason Category	Audited At	Auditor By	Called At	Called By	Notes
		Valid Transaction	Good Sale	7/2/2020 12:28​		7/1/2020 9:32​	Agent 1
		Valid Transaction	Good Sale	7/2/2020 12:30​		7/1/2020 9:52​	Agent 2
		Valid Transaction	Good Sale	7/2/2020 12:49​		7/1/2020 14:26​	Agent 2
		Valid Transaction	Good Sale	7/2/2020 12:26​		7/1/2020 9:26​	Agent 3
		Valid Transaction	Good Sale	7/2/2020 12:44​		7/1/2020 13:03​	Agent 3
		Valid Transaction	Good Sale	7/2/2020 12:28​		7/1/2020 9:42​	Agent 4
		Yellow - Missing required elements, permission to record and authorized user	Agent At Fault	7/2/2020 12:53​		7/1/2020 15:09​	Agent 5	Yellow Flag - Agent did not validate customers information in STARSS.
		Valid Transaction	Good Sale	7/2/2020 12:36​		7/1/2020 11:30​	Agent 6
		Yellow - Missing required elements, permission to record and authorized user	Agent At Fault	7/2/2020 12:42​		7/1/2020 12:39​	Agent 8	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 - Missing required elements, permission to record and authorized user	Agent At Fault	7/2/2020 12:58​		7/1/2020 16:18​	Agent 8	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

 

[Jclark031]

Here is an example of the breakdown tab

Agent	Valid	Yellow	Orange	Red	Total
Agent 1	10	3	0	0	13
Agent 2	12	3	1	0	16
Agent 3	18	2	0	0	20
Agent 4	12	2	0	0	14
Agent 5	6	5	0	0	11
Agent 6	5	2	0	0	7
Agent 7	0	6	1	0	7
Agent 8	2	9	0	1	12
Agent 9	13	1	0	0	14
Agent 10	8	2	0	0	10

 

[CA_Punit]

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1	Client Name	Product Name	Reason	Reason Category	Audited At	Auditor By	Called At	Called By	Notes	Sum No		Option 2 using Sumif		Agent No
2			Valid Transaction	Good Sale	7/2/2020 12:28		7/1/2020 9:32	Agent 1		1		2		Agent 5
3			Valid Transaction	Good Sale	7/2/2020 12:30		7/1/2020 9:52	Agent 2		1
4			Valid Transaction	Good Sale	7/2/2020 12:49		7/1/2020 14:26	Agent 2		1
5			Valid Transaction	Good Sale	7/2/2020 12:26		7/1/2020 9:26	Agent 3		1
6			Valid Transaction	Good Sale	7/2/2020 12:44		7/1/2020 13:03	Agent 3		1
7			Valid Transaction	Good Sale	7/2/2020 12:28		7/1/2020 9:42	Agent 4		1
8			Yellow - Missing required elements, permission to record and authorized user	Agent At Fault	7/2/2020 12:53		7/1/2020 15:09	Agent 5	Yellow Flag - Agent did not validate customers information in STARSS.	1
9			Valid Transaction	Good Sale	7/2/2020 12:36		7/1/2020 11:30	Agent 6		1
10			Yellow - Missing required elements, permission to record and authorized user	Agent At Fault	7/2/2020 12:42		7/1/2020 12:39	Agent 5	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	1
11			Yellow - Missing required elements, permission to record and authorized user	Agent At Fault	7/2/2020 12:58		7/1/2020 16:18	Agent 8	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	1
Sheet4

Cell Formulas
Range	Formula
L2	L2	=SUMIFS(J2:J11,H2:H11,N2,I2:I11,"*Yellow Flag*")

Is this you want

 

[pjmorris]

The K3 simply happened because that was where I'd pasted your example text. Having looked further at your data layout, try this to get the count of Yellow Flag for each agent, copy this to A2 and then copy down (note the 11 is the same as LEN("Yellow Flag"):

=SUMPRODUCT(--(H:H=A2),(LEN(I:I)-LEN(SUBSTITUTE(I:I,"Yellow Flag","")))/11)

 

[pjmorris]

This was the data set I worked with and the formula as shown above:

Book.xlsx
	A	B	C	D	E	F	G	H	I
1	Agent1	11						Agent1	Yellow Flag, Yellow Flag,
2	Agent2	40						Agent1	Yellow Flag, Yellow Flag, Yellow Flag, Yellow Flag, Yellow Flag, Yellow Flag, Yellow Flag,
3	Agent3	68						Agent1	Yellow Flag, Yellow Flag,
4	Agent4	30						Agent2	Yellow Flag, Yellow Flag, Yellow Flag, Yellow Flag, Yellow Flag, Yellow Flag, Yellow Flag,
5	Agent5	21						Agent2	Yellow Flag, Yellow Flag, Yellow Flag, Yellow Flag, Yellow Flag, Yellow Flag, Yellow Flag, Yellow Flag,
6	Agent6	41						Agent2	Yellow Flag, Yellow Flag, Yellow Flag, Yellow Flag, Yellow Flag, Yellow Flag, Yellow Flag, Yellow Flag, Yellow Flag, Yellow Flag, Yellow Flag,
7	Agent7	27						Agent2	Yellow Flag, Yellow Flag, Yellow Flag, Yellow Flag,
8	Agent8	54						Agent2	Yellow Flag, Yellow Flag, Yellow Flag, Yellow Flag, Yellow Flag, Yellow Flag, Yellow Flag, Yellow Flag, Yellow Flag, Yellow Flag,
9								Agent3	Yellow Flag, Yellow Flag, Yellow Flag, Yellow Flag, Yellow Flag, Yellow Flag, Yellow Flag,
10								Agent3	Yellow Flag, Yellow Flag, Yellow Flag,
11								Agent3	Yellow Flag, Yellow Flag, Yellow Flag,
12								Agent3	Yellow Flag, Yellow Flag, Yellow Flag, Yellow Flag, Yellow Flag, Yellow Flag, Yellow Flag, Yellow Flag, Yellow Flag, Yellow Flag, Yellow Flag, Yellow Flag, Yellow Flag, Yellow Flag, Yellow Flag,
13								Agent3	Yellow Flag, Yellow Flag, Yellow Flag, Yellow Flag, Yellow Flag,
14								Agent3	Yellow Flag, Yellow Flag, Yellow Flag, Yellow Flag, Yellow Flag, Yellow Flag, Yellow Flag, Yellow Flag, Yellow Flag, Yellow Flag, Yellow Flag, Yellow Flag,
15								Agent3	Yellow Flag, Yellow Flag, Yellow Flag, Yellow Flag, Yellow Flag, Yellow Flag, Yellow Flag, Yellow Flag, Yellow Flag, Yellow Flag,
16								Agent3	Yellow Flag, Yellow Flag, Yellow Flag, Yellow Flag, Yellow Flag, Yellow Flag, Yellow Flag, Yellow Flag, Yellow Flag, Yellow Flag, Yellow Flag, Yellow Flag, Yellow Flag,
17								Agent4	Yellow Flag, Yellow Flag, Yellow Flag, Yellow Flag, Yellow Flag, Yellow Flag, Yellow Flag, Yellow Flag, Yellow Flag, Yellow Flag, Yellow Flag,
18								Agent4	Yellow Flag, Yellow Flag, Yellow Flag, Yellow Flag, Yellow Flag, Yellow Flag,
19								Agent4	Yellow Flag, Yellow Flag, Yellow Flag, Yellow Flag, Yellow Flag, Yellow Flag, Yellow Flag, Yellow Flag, Yellow Flag, Yellow Flag, Yellow Flag,
20								Agent4	Yellow Flag, Yellow Flag,
21								Agent5	Yellow Flag, Yellow Flag, Yellow Flag, Yellow Flag, Yellow Flag, Yellow Flag, Yellow Flag, Yellow Flag, Yellow Flag,
22								Agent5
23								Agent5	Yellow Flag, Yellow Flag,
24								Agent5	Yellow Flag,
25								Agent5	Yellow Flag, Yellow Flag, Yellow Flag, Yellow Flag, Yellow Flag, Yellow Flag, Yellow Flag, Yellow Flag, Yellow Flag,
26								Agent6	Yellow Flag, Yellow Flag, Yellow Flag, Yellow Flag, Yellow Flag, Yellow Flag, Yellow Flag, Yellow Flag, Yellow Flag, Yellow Flag,
27								Agent6
28								Agent6	Yellow Flag, Yellow Flag, Yellow Flag, Yellow Flag, Yellow Flag, Yellow Flag, Yellow Flag, Yellow Flag, Yellow Flag, Yellow Flag,
29								Agent6	Yellow Flag, Yellow Flag, Yellow Flag, Yellow Flag, Yellow Flag, Yellow Flag, Yellow Flag, Yellow Flag, Yellow Flag, Yellow Flag, Yellow Flag, Yellow Flag, Yellow Flag,
30								Agent6	Yellow Flag, Yellow Flag, Yellow Flag, Yellow Flag, Yellow Flag, Yellow Flag, Yellow Flag, Yellow Flag,
31								Agent7	Yellow Flag, Yellow Flag, Yellow Flag, Yellow Flag, Yellow Flag, Yellow Flag, Yellow Flag, Yellow Flag, Yellow Flag, Yellow Flag, Yellow Flag, Yellow Flag,
32								Agent7	Yellow Flag, Yellow Flag, Yellow Flag, Yellow Flag, Yellow Flag, Yellow Flag, Yellow Flag, Yellow Flag, Yellow Flag, Yellow Flag, Yellow Flag, Yellow Flag, Yellow Flag, Yellow Flag, Yellow Flag,
33								Agent8	Yellow Flag, Yellow Flag, Yellow Flag,
34								Agent8	Yellow Flag, Yellow Flag, Yellow Flag, Yellow Flag, Yellow Flag, Yellow Flag, Yellow Flag, Yellow Flag, Yellow Flag, Yellow Flag, Yellow Flag, Yellow Flag,
35								Agent8	Yellow Flag, Yellow Flag, Yellow Flag, Yellow Flag, Yellow Flag, Yellow Flag, Yellow Flag, Yellow Flag, Yellow Flag, Yellow Flag, Yellow Flag, Yellow Flag, Yellow Flag, Yellow Flag, Yellow Flag,
36								Agent8	Yellow Flag,
37								Agent8	Yellow Flag, Yellow Flag, Yellow Flag,
38								Agent8	Yellow Flag, Yellow Flag, Yellow Flag, Yellow Flag, Yellow Flag, Yellow Flag, Yellow Flag, Yellow Flag, Yellow Flag, Yellow Flag,
39								Agent8	Yellow Flag, Yellow Flag, Yellow Flag, Yellow Flag, Yellow Flag, Yellow Flag, Yellow Flag, Yellow Flag, Yellow Flag, Yellow Flag,
Sheet2

Cell Formulas
Range	Formula
B1:B8	B1	=SUMPRODUCT(--(H1:H39=A1),(LEN(I1:I39)-LEN(SUBSTITUTE(I1:I39,"Yellow Flag","")))/11)

 

[Jclark031]

That works perfectly! Thank you both so much for the help!

 

[pjmorris]

Thanks for the feedback and delighted to have helped.