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.

Improve this question
2
  • Why don't you share a doc with us and show us the expected outcome.
    – Jacob Jan
    Commented Sep 22, 2013 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
    Commented Sep 22, 2013 at 18:54

1 Answer 1

Reset to default
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

Improve this answer
2
  • That is perfect! Could you explain how the CONTINUE works?
    – aslum
    Commented Sep 22, 2013 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
    Commented Sep 22, 2013 at 19:37

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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