I've got a ton of cells (say 6x20) that have various names in them. I'd like to total the number of times a name in another field matches any of the other cells.

``````Alice  Bob    Claire
Doug   Alice  Chris
Bob    Claire Bob
``````

It seems like there should be a way to look at the example 3x3 above (or my actually much larger group of names) and extract how many "Bob"s or "Alice"s or whatever occur. My Spreadsheet-Fu is weak and I haven't been able to find an answer via Google (probably because I just don't know the right term for what I want to do).

Here's a sample of what I'm trying for: Count Occurence

It uses IF statements, but since EACH cell needs it's own IF statement this is not really a viable way to make the spreadsheet, especially as more people can be added in at later points.

• Why don't you share a doc with us and show us the expected outcome. – Jacob Jan Tuinstra Sep 22 '13 at 18:37
• There's an example (only Bob and Alice's multipliers are calculated since it's a pain to construct this way). – aslum Sep 22 '13 at 18:54

Add the following formula in cell `F2`:

## Formula

``````=ARRAYFORMULA(COUNTIF(C2:E5;B2:B5))
``````

## Explained

`C2:E5` is the range in which the search is to be performed. `B2:B5` are the search parameters. The `COUNTIF` function counts the occurrences and the `ARRAYFORMULA` will take on the complete range.

## Screenshot • That is perfect! Could you explain how the CONTINUE works? – aslum Sep 22 '13 at 19:34
• @aslum That a continuation of the range (array). You can access that via the `INDEX` function, see your example file. – Jacob Jan Tuinstra Sep 22 '13 at 19:37