footie prediction results sheet -help required please

Can someone amend the is sheet ,so it does the following please ?( i have to do this manually ,at the mo)


Basically there are 31 players ,each predicts Home win (H) /Away win (A) or draw for each match (D)


* In column B ,I need the total amount of correct predictions for each person

* I need each correct guess highlighted (preferably in the same colour)

* if all 5 correct then all 5 and the player name highlighted in a different colour (yellow)-if all 5 right,the player wins all the money !!.....if not,it rollsover...

Hopefully someone can do this for me

Cheers Bruce

FYI,the number of players can change sometimes,so will it be just delete /insert new line as appropriate ?

If a player is late and no entry as in line 36 ,will this remain blank ?

In the attached file I've used this formula in cell B7:

=SUMPRODUCT((C7:K7=$C$5:$K$5)*(C7:K7<>""))

This is copied down to the bottom of your data.

I've set up conditional formatting across the range $C$7:$K$37 to turn the cells green if they have the correct result. I've also set up CF across the range $A$7:$L$37 to turn the row red with a white foreground if the score for that row is 5.

I've also adjusted some of the data so you can see the effect when all 5 have been chosen correctly.

Hope this helps.

Pete

absolutely spot on,Pete

This will be a great help....it looks easy to predict all 5 matches right,but we had a 14 week rollover last year !!

Thanks very much

Bruce

Glad to help, Bruce.

If that takes care of your original question, please take a moment to select Thread Tools from the menu above and to the right of your first post in this thread, and mark the thread as SOLVED.

You might also like to know that you can directly thank those who have helped you by clicking on the small "star" icon located in the lower left corner of a post that you have found to be helpful (not just in this thread - for any post that has helped you). This also adds to the reputation of the poster (the small green bars in the poster's profile).

Pete