Take the 2-minute tour ×
Web Applications Stack Exchange is a question and answer site for power users of web applications. It's 100% free, no registration required.

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
add comment

1 Answer

up vote 4 down vote accepted

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

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
add comment

Your Answer

 
discard

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.