Web Applications Stack Exchange is a question and answer site for power users of web applications. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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.

share|improve this question
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
up vote 7 down vote accepted

Add the following formula in cell F2:




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.


enter image description here

share|improve this answer
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

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.