Multiply values from different cells in a single cell

Copper Contributor

G'day, 

Very much an Excel novice, but i am learning. I'm a bit stuck on this one though. It's for a group of mates who go in a footy pools comp each year. Each player is allocated at least 3 numbers, if if those numbers coincide with the winning score of a footy game that week, they get $10, There are 9 games a week, and 23 rounds in a season. I have been able to get a single cell from a single match to work, but I want to multiple several matches if possible.

For example, Player A is allocated 10, 20 and 30. If one of the matches is a 10 point result, then great, he wins $10. If in three of the games though, the result is 10 points, 20 points and 30 points, he gets $30. I want the cell to reflect that total amount without a ton of hidden cells etc. I the attachment we have:

Sheet 1 - BUSHFOOTY

Column A - List of players
Column B to F - the allocated numbers
Column H - Round 1 (Rd1)
Column I - Round 2 (Rd2)
Column J - Round 3 (Rd 3)

Sheet 2 - Rd1

Column A - the matches
Column B - the results

As an example I have done =IF(COUNTIF(B3:F3,'RD1'!B2),10,"") in Cell H3, but that was sort of guessing. It only deals with one match though. I'd like it to calculate all 9 matches each week

Any help would be greatly appreciated

Cheers
DH

0 Replies