How do I count occurrences of a word over many rows with Google Spreadsheet

Question

I have a spreadsheet like this:

|     A     |    B   |        C        |
+-----------+--------+-----------------+
| Date      | Place  | Names           |
+-----------+--------+-----------------+
| 1/2/2013  | Place1 | John, Jane, Bob |
| 2/5/2013  | Place2 | Jane, Doug      |
| 3/8/2013  | Place3 | John, Jane      |
| 4/15/2013 | Place4 | Doug, Mark, Bob |

I want, in another sheet, to count the number of times a name (or word, basically) appears in column C on Sheet1, but the list needs to be dynamic. I could easily do a =COUNTIF(Sheet1!C:C, "*John*") to return 2, but then I have to do that for every person who appears in column C. So I first need to obtain a list of unique names in column C, split by comma, then do a count on each of those names and print them out alphabetically to look like this:

|     A     |   B   |
+-----------+-------+
| Name      | Count |
+-----------+-------+
| Bob       |   2   |
| Doug      |   2   |
| Jane      |   3   |
| John      |   2   |
| Mark      |   1   |

Possible duplicate of [Getting the count and unique values of a column of comma separated values?](https://stackoverflow.com/questions/20715099/getting-the-count-and-unique-values-of-a-column-of-comma-separated-values) — Rubén, Jun 29 '18 at 16:06

score -1 · Accepted Answer · edited May 23 '17 at 12:01

-1

Take a look at this thread; ColA is the Names list; ColB is your name list and ColC is the count

edited May 23 '17 at 12:01

Community

1
1

answered Jan 03 '14 at 05:17

Chris

868
7
6

score -2 · Answer 2 · answered Jan 03 '14 at 05:48

I am not clear about the spreadsheet, but I have alternative solution for your problem. What I did, I put that data in simple txt file , I executed following command to getting
output as per your needs

Note : second cut command to use to remove the leading spaces

How do I count occurrences of a word over many rows with Google Spreadsheet

2 Answers2