Tally wins-losses in Excel

[patme]

Okay,

I've tried and tried to figure this out, and I can't, so I'm coming to the experts. One of these days I hope to give back to this list as much as I've learned from it.

I have a wrestling program set up. I have 6 kids wrestling. Each kid wrestles one of the other kids once. I have a bout card set up in Excel in order to record the score, thus determining the winner. What I need is a way to add the wins/losses up for each kid. The problem is, the bout cards are not in any alphabetical order, and have several spaces between each bout card, etc., so I think VLOOKUP is out of the question.

Here's what I have, in a nutshell.
Q66 - kid1 (actually, Q66 is merged with 67 & 68, and R & S, as will the size go for all of the kid name cells in my examples)
Q70 - kid 2
those 2 kids wrestle, and one wins, and I want to be able to ad a "1" next to Kid1's name in a list I have somewhere else (say A66).

The same scenario applies to:
Q84 - kid3
Q88 - kid4

Q102 - kid5
Q106 - kid6

Now, here's the really fun part. Once all of these kids wrestle once, they wrestle again against someone else.

So now I have:
Q120 - kid1
Q124 - kid6

etc.!

I know...I'm not asking much, but the last time I did, I got the perfect answer, so I'm hoping for something close this time.

Thanks.

 

[AnotherHiggins]

For your example data:
[COLOR=blue white]=sumproduct((Q66:Q124="kid1")*(A66:A124))[/color]

NOTE: The ranges in each section must be the same size, and you cannot use an entire column (A:A).

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ181-2886 before posting.

 

[patme]

Found an answer, in part thanks to Anotherhiggins.

=SUMPRODUCT(($Q$66:$Q$322=Rnd1M1W1)*($AE$66:$AE$322<>""))

Q66:Q322 is my range of kid names.
Rnd1m1w1 is the name for a cell that I'm using to track kid1
AE66:AE322 is the range that I'm putting a "1" in if the kid wins.

What I did figure out was that since my kid1 name box was actually 3 rows x 3 cols, I had to make the win box (AE:66, for example) 3 rows large as well. Once I did that...voila!