0

I'm trying to count the number of times specific words, names in this case, occurs in a column. However, there may be any number of names in each cell in that column, and I'm only interested in the first one in each.

If there is more than one name in a cell, each of them is separated by a comma, and I'm hoping to use that in some way to ignore the names I don't want. It's very much like this question, the only difference is discarding everything after a comma.

Is there a way to do this in Sheets?

Community
  • 1
  • 1
GZ_0
  • 27
  • 5

1 Answers1

0

Does this formula work as you want (assuming your list of names is in the range A2:A):

=QUERY({ArrayFormula(IFERROR(LEFT(A2:A,SEARCH(",",A2:A)-1),A2:A)),A2:A},"select Col1, count(Col2) where Col1 <> '' group by Col1 label Col1 'Name'")

Chris Hick
  • 3,004
  • 1
  • 13
  • 15
  • It's exactly what I want! I was wondering if there's a way to customize the title of the second column though? It's just called "count" right now. – GZ_0 Jun 26 '15 at 16:27
  • yes, you can amend the end of the formula: label Col1 'Name', count(Col2) 'Your own label here'") – Chris Hick Jun 26 '15 at 17:59