5

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
10

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

enter image description here

  • 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 clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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