Gift Giving guessing game point accumulation help

I have been tasked to come up with a point tallying system that came out of my family's Christmas Secret Santa effort as i did it on pen and paper this year and it took me forever and a day to do and therefore missed out on serious eating/drinking time.

Effectively every year rather than give our entire family a present each we do Secret Santa, so get allocated a person randomly to spend a £ amount on and then we play a game afterwards to see if we can guess who the Secret Santa was and allocate points according to how well we can guess.

The points system is 2 points for a correct guess, 3 points (to the gift giver) if nobody guesses the correct gift giver plus 1 point to each person nominated that wasn’t the gift giver

See the attached template. Range C2:L2 are the guessers. B3:B12 are the presents they received and need to guess whom gifted them to them. C3:L13 are the guesses that C2:L2 made.

O2:X13 are the correct answers. For example Edith was the giver of the voucher to Delroy.

Row 20 onwards is the scores (where i will really struggle!)
In the above example nobody guesses Edith so Edith scores 3, Isaac would get three also (having been guessed that number of times), Ana gets 2, as do Harold and Gary, and Frank, having only been guessed once gets 1.

In another example, the gift of the shoes: Charlie was the gift giver and he was guessed correctly by Ana and Harold who get 2 points each. In this example harold gets a further point as he was guessed as the gift giver by Charlie, and the other people who were guessed get a point for the times they were guessed.

Hopefully this makes sense, but i cant seem to get a handle on how to code the points system as outlined above into my template.

6StringJazzer

All good points - I will try to clarify:
Row range C2:L2 are the players/receivers of the gifts.(as are O2:X2)
The names below each row in the range C2:L2 are the guesses the players in C2:L2 made for the corresponding gifts in range B3:B12. Ie Ana guessed that Edith gave the Pen (incorrect),Charlie gave the shoes (correct), Bob gave the chocs (incorrect) etc.

as for how you can submit a guess for a gift if you gave it, well that's part of the game as you have to bluff and guess someone else (perhaps swaying others to guess as you did and so noone guesses you, granting you 3 points). You should never be able to guess yourself, which hopefully i did not do in my attached template.So you should never be able to guess correctly for your own gift. (that would be cheating!)

you HAVE to have a duplicate name in your list, as you are not allowed to guess yourself at any stage you need to supplement your name that you would associate with your own present with someone else. If you want to guess the same person for every gift that should be allowed, but it wont get you many points.

Unused guesses was my next step, there i want to create a macro to tell the user what names a particular player has not yet guessed from the list of players. (ie if Ana has guessed Bob, Charlie, Delroy and Edith already, the list should be a list of the remaining names of the players, ie Frank, gary, harold, Isaac and Jessie. Her own name (ana) should never appear as you cant ever guess your own name)

I think this gets you what you need, please review and let me know.

In row 23 I am using array formulas, a tricky bit of business if you haven't seen them before.

The unused guesses list doesn't require a macro if you're flexible on formatting. Also done in the attached. Note that it's blank because everybody has guessed everybody.

If you need to add more columns/rows because the number of people increases, you will need to adjust formulas accordingly.

Merry Christmas!

i ....... LOVE.....IT.

merry xmas indeed. thank you so much. Next year I will have so much more time to fill my belly with food and wine!

thank you so much again. Rep added....solved.

You're welcome! Thanks for the rep, and thanks for marking your thread Solved!