Home

Multiply values from different cells in a single cell

%3CLINGO-SUB%20id%3D%22lingo-sub-357940%22%20slang%3D%22en-US%22%3EMultiply%20values%20from%20different%20cells%20in%20a%20single%20cell%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-357940%22%20slang%3D%22en-US%22%3E%3CP%3E%3CSPAN%3EG'day%2C%26nbsp%3B%3C%2FSPAN%3E%3CBR%20%2F%3E%3CBR%20%2F%3E%3CSPAN%3EVery%20much%20an%20Excel%20novice%2C%20but%20i%20am%20learning.%20I'm%20a%20bit%20stuck%20on%20this%20one%20though.%20It's%20for%20a%20group%20of%20mates%20who%20go%20in%20a%20footy%20pools%20comp%20each%20year.%20Each%20player%20is%20allocated%20at%20least%203%20numbers%2C%20if%20if%20those%20numbers%20coincide%20with%20the%20winning%20score%20of%20a%20footy%20game%20that%20week%2C%20they%20get%20%2410%2C%20There%20are%209%20games%20a%20week%2C%20and%2023%20rounds%20in%20a%20season.%20I%20have%20been%20able%20to%20get%20a%20single%20cell%20from%20a%20single%20match%20to%20work%2C%20but%20I%20want%20to%20multiple%20several%20matches%20if%20possible.%3C%2FSPAN%3E%3CBR%20%2F%3E%3CBR%20%2F%3E%3CSPAN%3EFor%20example%2C%20Player%20A%20is%20allocated%2010%2C%2020%20and%2030.%20If%20one%20of%20the%20matches%20is%20a%2010%20point%20result%2C%20then%20great%2C%20he%20wins%20%2410.%20If%20in%20three%20of%20the%20games%20though%2C%20the%20result%20is%2010%20points%2C%2020%20points%20and%2030%20points%2C%20he%20gets%20%2430.%20I%20want%20the%20cell%20to%20reflect%20that%20total%20amount%20without%20a%20ton%20of%20hidden%20cells%20etc.%20I%20the%20attachment%20we%20have%3A%3C%2FSPAN%3E%3CBR%20%2F%3E%3CBR%20%2F%3E%3CSTRONG%3ESheet%201%20-%20BUSHFOOTY%3C%2FSTRONG%3E%3CBR%20%2F%3E%3CBR%20%2F%3E%3CSPAN%3EColumn%20A%20-%20List%20of%20players%3C%2FSPAN%3E%3CBR%20%2F%3E%3CSPAN%3EColumn%20B%20to%20F%20-%20the%20allocated%20numbers%3C%2FSPAN%3E%3CBR%20%2F%3E%3CSPAN%3EColumn%20H%20-%20Round%201%20(Rd1)%3C%2FSPAN%3E%3CBR%20%2F%3E%3CSPAN%3EColumn%20I%20-%20Round%202%20(Rd2)%3C%2FSPAN%3E%3CBR%20%2F%3E%3CSPAN%3EColumn%20J%20-%20Round%203%20(Rd%203)%3C%2FSPAN%3E%3CBR%20%2F%3E%3CBR%20%2F%3E%3CSTRONG%3ESheet%202%20-%20Rd1%3C%2FSTRONG%3E%3CBR%20%2F%3E%3CBR%20%2F%3E%3CSPAN%3EColumn%20A%20-%20the%20matches%3C%2FSPAN%3E%3CBR%20%2F%3E%3CSPAN%3EColumn%20B%20-%20the%20results%3C%2FSPAN%3E%3CBR%20%2F%3E%3CBR%20%2F%3E%3CSPAN%3EAs%20an%20example%20I%20have%20done%20%3DIF(COUNTIF(B3%3AF3%2C'RD1'!B2)%2C10%2C%22%22)%20in%20Cell%20H3%2C%20but%20that%20was%20sort%20of%20guessing.%20It%20only%20deals%20with%20one%20match%20though.%20I'd%20like%20it%20to%20calculate%20all%209%20matches%20each%20week%3C%2FSPAN%3E%3CBR%20%2F%3E%3CBR%20%2F%3E%3CSPAN%3EAny%20help%20would%20be%20greatly%20appreciated%3C%2FSPAN%3E%3CBR%20%2F%3E%3CBR%20%2F%3E%3CSPAN%3ECheers%3C%2FSPAN%3E%3CBR%20%2F%3E%3CSPAN%3EDH%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-357940%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E
Highlighted
Occasional Visitor

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