Need help with formula in football pool spreadsheet

[Kfain]

I used Excel in my job years ago and have just lost my touch for th
formulas. This should be a cinch for the seasoned Excel user.

My husband and I do an NFL football pool each year where each of te
people bet a dime on every NFL game for the entire year. We hav
developed the spreadsheet, but we're having trouble getting th
formulas for the initial figuring of wins/losses picked per person pe
game. The trick to our spreadsheet is figuring how much each perso
owes/gets, but it has to be figured PER GAME. That's 16 games per wee
times 10 people in the pool. I have attached the spreadsheet and nee
help with cells C21 and C22. I've been told I am wanting Excel t
figure too many things in the cells and it won't work. I think it i
powerful enough, I just don't know how to lay out the formula. An
help would be appreciated. Remember, you can't just total the person'
wins/losses for that week, it has to be figured per person per game.
Also, ignore Jamie in the sheet. He is a new player this year an
didn't play last year and I just put in the figures for an actual wee
from last year.

I've attached a sample week from last year. A (1) in a cell is wher
the person picked the winning team for that game and a (2) is wher
they picked a loser. Columns M and N total the number of wins o
losses picked per game.

What C21 cell needs to do is total the number of wins (1's) in C3:C1
per the CORRESPONDING cells in column N. In other words total th
numbers of the N column IF THERE IS A 1 in the C column. On th
attached spreadsheet the correct number to go here is 20.

Simarlarly C22 needs to total the number of losses (2's) C3:C18 per th
CORRESPONDING cells in column M. In other words total the numbers o
the N column IF THERE IS A 2 in the C column. The correct number to g
here is 11.

The key here is to total how many games were there where you picked th
winner, and someone else picked the loser (amount you are owed) AND ho
many games were there where you picked the loser, and someone els
picked the winner (amount you owe).

I'm still working on the other totals, but if I can get these tw
formulas, I think I can get the rest.

Thanks to anyone who can help

Attachment filename: football_edited.xls
Download attachment: http://www.excelforum.com/attachment.php?postid=66314

 

[Max]

One way ..:

Put:

In C21: =SUMPRODUCT(--(C$3:C$18=1),$N$3:$N$18)
In C22: =SUMPRODUCT(--(C$3:C$18=2),$M$3:$M$18)

This seems to return the correct resulting values
of 20 / 11 in C21 / C22 as mentioned in your post

Select C21:C22, copy across as desired
--
Alternatively, you could also try:

In C21: =SUM(IF(C$3:C$18=1,$N$3:$N$18))
and array-enter (i.e. Press CTRL+SHIFT+ENTER)
instead of just pressing ENTER

In C22: =SUM(IF(C$3:C$18=2,$M$3:$M$18))
Array-enter (i.e. Press CTRL+SHIFT+ENTER)

Excel will wrap curly braces { } around the formulas
(don't enter these braces in the formula!)

which will return identical results

Select C21:C22, copy across as desired
--

Of the 2, the SUMPRODUCT way is preferred,
going by the ng's experience ..

 

[Kfain]

YOU ARE THE MAN!!!!! It worked perfectly!! Don't know how to thank yo
enough! Since I have you here....

I figured row 30, based on one dime per game, but now need to know ho
to make the totals continue from week to week.

In rows 27 and 28 I'd like to keep a running total of how man
wins/losses each person picked and...

In row 32 how much money that person owes or is owed to date during th
season...

I.E. For Tim, I would be adding Cells C24 and C25 to what's already i
C27 and C28 to figure the wins/losses and similarly for the money i
C30, totalling in C32.

Any ideas here? Again, you seem like a total pro, so this is mos
likely a cinch!!! Thanks again!!!

I'm attaching the spreadsheet with your formula in the given spots!

Attachment filename: football_edited.xls
Download attachment: http://www.excelforum.com/attachment.php?postid=66332

 

[Max]

Glad to know it worked,
and thanks for the feedback

Sorry, I'm out of suggestions on your follow-on Qs

Think you'd need a macro to cumulate the
running totals. Perhaps you might consider
posting in the .programming group ..

Try posting just in plain text, w/o any attachment
And do not rely overly on providing links to files
as a substitute for plain text description ..

 

[Kfain]

Thanks for your advice. I'll do that!! Have a great day