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

#### Jclark031

##### New Member
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

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

### Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a \$25,000 loan, 5% annual interest, 60 month loan.

#### CA_Punit

##### Well-known Member
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

##### New Member
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

##### New Member
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

##### Well-known Member

Book1
ABCDEFGHIJKLMN
1Client NameProduct NameReasonReason CategoryAudited AtAuditor ByCalled AtCalled ByNotesSum NoOption 2 using SumifAgent No
2Valid TransactionGood Sale7/2/2020 12:287/1/2020 9:32Agent 112Agent 5
3Valid TransactionGood Sale7/2/2020 12:307/1/2020 9:52Agent 21
4Valid TransactionGood Sale7/2/2020 12:497/1/2020 14:26Agent 21
5Valid TransactionGood Sale7/2/2020 12:267/1/2020 9:26Agent 31
6Valid TransactionGood Sale7/2/2020 12:447/1/2020 13:03Agent 31
7Valid TransactionGood Sale7/2/2020 12:287/1/2020 9:42Agent 41
8Yellow - Missing required elements, permission to record and authorized userAgent At Fault7/2/2020 12:537/1/2020 15:09Agent 5Yellow Flag - Agent did not validate customers information in STARSS.1
9Valid TransactionGood Sale7/2/2020 12:367/1/2020 11:30Agent 61
10Yellow - Missing required elements, permission to record and authorized userAgent At Fault7/2/2020 12:427/1/2020 12:39Agent 5Yellow 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 name1
11Yellow - Missing required elements, permission to record and authorized userAgent At Fault7/2/2020 12:587/1/2020 16:18Agent 8Yellow 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 name1
Sheet4
Cell Formulas
RangeFormula
L2L2=SUMIFS(J2:J11,H2:H11,N2,I2:I11,"*Yellow Flag*")

Is this you want

#### pjmorris

##### Well-known Member
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

##### Well-known Member

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

Book.xlsx
ABCDEFGHI
1Agent111Agent1Yellow Flag, Yellow Flag,
2Agent240Agent1Yellow Flag, Yellow Flag, Yellow Flag, Yellow Flag, Yellow Flag, Yellow Flag, Yellow Flag,
3Agent368Agent1Yellow Flag, Yellow Flag,
4Agent430Agent2Yellow Flag, Yellow Flag, Yellow Flag, Yellow Flag, Yellow Flag, Yellow Flag, Yellow Flag,
5Agent521Agent2Yellow Flag, Yellow Flag, Yellow Flag, Yellow Flag, Yellow Flag, Yellow Flag, Yellow Flag, Yellow Flag,
6Agent641Agent2Yellow Flag, Yellow Flag, Yellow Flag, Yellow Flag, Yellow Flag, Yellow Flag, Yellow Flag, Yellow Flag, Yellow Flag, Yellow Flag, Yellow Flag,
7Agent727Agent2Yellow Flag, Yellow Flag, Yellow Flag, Yellow Flag,
8Agent854Agent2Yellow Flag, Yellow Flag, Yellow Flag, Yellow Flag, Yellow Flag, Yellow Flag, Yellow Flag, Yellow Flag, Yellow Flag, Yellow Flag,
9Agent3Yellow Flag, Yellow Flag, Yellow Flag, Yellow Flag, Yellow Flag, Yellow Flag, Yellow Flag,
10Agent3Yellow Flag, Yellow Flag, Yellow Flag,
11Agent3Yellow Flag, Yellow Flag, Yellow Flag,
12Agent3Yellow 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,
13Agent3Yellow Flag, Yellow Flag, Yellow Flag, Yellow Flag, Yellow Flag,
14Agent3Yellow Flag, Yellow Flag, Yellow Flag, Yellow Flag, Yellow Flag, Yellow Flag, Yellow Flag, Yellow Flag, Yellow Flag, Yellow Flag, Yellow Flag, Yellow Flag,
15Agent3Yellow Flag, Yellow Flag, Yellow Flag, Yellow Flag, Yellow Flag, Yellow Flag, Yellow Flag, Yellow Flag, Yellow Flag, Yellow Flag,
16Agent3Yellow 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,
17Agent4Yellow Flag, Yellow Flag, Yellow Flag, Yellow Flag, Yellow Flag, Yellow Flag, Yellow Flag, Yellow Flag, Yellow Flag, Yellow Flag, Yellow Flag,
18Agent4Yellow Flag, Yellow Flag, Yellow Flag, Yellow Flag, Yellow Flag, Yellow Flag,
19Agent4Yellow Flag, Yellow Flag, Yellow Flag, Yellow Flag, Yellow Flag, Yellow Flag, Yellow Flag, Yellow Flag, Yellow Flag, Yellow Flag, Yellow Flag,
20Agent4Yellow Flag, Yellow Flag,
21Agent5Yellow Flag, Yellow Flag, Yellow Flag, Yellow Flag, Yellow Flag, Yellow Flag, Yellow Flag, Yellow Flag, Yellow Flag,
22Agent5
23Agent5Yellow Flag, Yellow Flag,
24Agent5Yellow Flag,
25Agent5Yellow Flag, Yellow Flag, Yellow Flag, Yellow Flag, Yellow Flag, Yellow Flag, Yellow Flag, Yellow Flag, Yellow Flag,
26Agent6Yellow Flag, Yellow Flag, Yellow Flag, Yellow Flag, Yellow Flag, Yellow Flag, Yellow Flag, Yellow Flag, Yellow Flag, Yellow Flag,
27Agent6
28Agent6Yellow Flag, Yellow Flag, Yellow Flag, Yellow Flag, Yellow Flag, Yellow Flag, Yellow Flag, Yellow Flag, Yellow Flag, Yellow Flag,
29Agent6Yellow 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,
30Agent6Yellow Flag, Yellow Flag, Yellow Flag, Yellow Flag, Yellow Flag, Yellow Flag, Yellow Flag, Yellow Flag,
31Agent7Yellow Flag, Yellow Flag, Yellow Flag, Yellow Flag, Yellow Flag, Yellow Flag, Yellow Flag, Yellow Flag, Yellow Flag, Yellow Flag, Yellow Flag, Yellow Flag,
32Agent7Yellow 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,
33Agent8Yellow Flag, Yellow Flag, Yellow Flag,
34Agent8Yellow Flag, Yellow Flag, Yellow Flag, Yellow Flag, Yellow Flag, Yellow Flag, Yellow Flag, Yellow Flag, Yellow Flag, Yellow Flag, Yellow Flag, Yellow Flag,
35Agent8Yellow 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,
36Agent8Yellow Flag,
37Agent8Yellow Flag, Yellow Flag, Yellow Flag,
38Agent8Yellow Flag, Yellow Flag, Yellow Flag, Yellow Flag, Yellow Flag, Yellow Flag, Yellow Flag, Yellow Flag, Yellow Flag, Yellow Flag,
39Agent8Yellow Flag, Yellow Flag, Yellow Flag, Yellow Flag, Yellow Flag, Yellow Flag, Yellow Flag, Yellow Flag, Yellow Flag, Yellow Flag,
Sheet2
Cell Formulas
RangeFormula
B1:B8B1=SUMPRODUCT(--(H1:H39=A1),(LEN(I1:I39)-LEN(SUBSTITUTE(I1:I39,"Yellow Flag","")))/11)

#### Jclark031

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

#### pjmorris

##### Well-known Member
Thanks for the feedback and delighted to have helped.

Replies
24
Views
1K
Replies
3
Views
95
Replies
3
Views
46
Replies
1
Views
82
Replies
2
Views
66

1,127,472
Messages
5,624,925
Members
416,064
Latest member
PaulBr2

### 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