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

Jclark031

New Member
Joined
Jul 15, 2020
Messages
9
Office Version
  1. 2013
Platform
  1. Windows
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
Joined
Jul 15, 2020
Messages
9
Office Version
  1. 2013
Platform
  1. Windows
I'm putting together a mock sheet that I can share. I'm just taking out private information from it.
 

Some videos you may like

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
Joined
Nov 18, 2019
Messages
893
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Jul 15, 2020
Messages
9
Office Version
  1. 2013
Platform
  1. Windows
Here is the Data Tab

Client NameProduct NameReasonReason CategoryAudited AtAuditor ByCalled AtCalled ByNotes
Valid TransactionGood Sale
7/2/2020 12:28​
7/1/2020 9:32​
Agent 1
Valid TransactionGood Sale
7/2/2020 12:30​
7/1/2020 9:52​
Agent 2
Valid TransactionGood Sale
7/2/2020 12:49​
7/1/2020 14:26​
Agent 2
Valid TransactionGood Sale
7/2/2020 12:26​
7/1/2020 9:26​
Agent 3
Valid TransactionGood Sale
7/2/2020 12:44​
7/1/2020 13:03​
Agent 3
Valid TransactionGood Sale
7/2/2020 12:28​
7/1/2020 9:42​
Agent 4
Yellow - Missing required elements, permission to record and authorized userAgent At Fault
7/2/2020 12:53​
7/1/2020 15:09​
Agent 5Yellow Flag - Agent did not validate customers information in STARSS.
Valid TransactionGood Sale
7/2/2020 12:36​
7/1/2020 11:30​
Agent 6
Yellow - Missing required elements, permission to record and authorized userAgent At Fault
7/2/2020 12:42​
7/1/2020 12:39​
Agent 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 name
Yellow - Missing required elements, permission to record and authorized userAgent At Fault
7/2/2020 12:58​
7/1/2020 16:18​
Agent 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 name
 

Jclark031

New Member
Joined
Jul 15, 2020
Messages
9
Office Version
  1. 2013
Platform
  1. Windows
Here is an example of the breakdown tab

AgentValidYellowOrangeRedTotal
Agent 11030013
Agent 21231016
Agent 31820020
Agent 41220014
Agent 5650011
Agent 652007
Agent 706107
Agent 8290112
Agent 91310014
Agent 10820010
 

CA_Punit

Well-known Member
Joined
Nov 18, 2019
Messages
893
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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
Joined
Aug 2, 2012
Messages
1,988
Office Version
  1. 2016
Platform
  1. Windows
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
Joined
Aug 2, 2012
Messages
1,988
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

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
Joined
Jul 15, 2020
Messages
9
Office Version
  1. 2013
Platform
  1. Windows
That works perfectly! Thank you both so much for the help!
 

pjmorris

Well-known Member
Joined
Aug 2, 2012
Messages
1,988
Office Version
  1. 2016
Platform
  1. Windows
Thanks for the feedback and delighted to have helped.
 

Watch MrExcel Video

Forum statistics

Threads
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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top