CODEWITHSUNDEEP
excel
sayth
sayth

Reputation: 7048

Count times a name matches a code in excel

What I am trying to do is loop through a column of names (includes repeated names) and create a count of the times they appear and a call code matches.

This is my source table

EventID Name    Code
1811    Tom     CC,CA,CS
1809    Jane    CA,
1523    Bill    CS,CA
1637    Tom     CA,CC
1514    Jane    CS,CA,CC
1728    Bill    CC,CA
1397    Sam     CC
1873    Sally   CS

For example in the below table I want to match all the times Tom appears with the call code CC, in this instance 2.

Name    Count (CC)
Tom     2
Jane    
Bill    
Sam 
Sally

I can search the text to see if CC exists in the list. My source column is O in the below.

=SUMPRODUCT(--ISNUMBER(SEARCH("CC",O6)))

Cannot figure how to get it to sum for each occurrence of a person however.

Upvotes: 1

Views: 36

Answers (1)

pnuts
pnuts

Reputation: 59485

Maybe something like:

=COUNTIFS(L:L,L10,M:M,"=*CC*")

provided "CC" is limited to once per cell.

Upvotes: 3

Related Questions