Formula to calculate Points earned




I am looking for one formula to count the points as explained in the attached workbook.


I can do it but I have use helper columns.


Thanks in advance.

5 Replies



You can try this formula. Enter the formula with ctrl+shift+enter if you don't work with Office365 or Excel 2021 or Excel online.


thank you !

When I remove the countries (leave blank) that somebody has written, I get 9 points. This is a possibility for guys who will fill nothing.
Sorry I did not inform earlier but I use office 2019 which works OK with the formula.
best response confirmed by A_SIRAT (Contributor)



You can try this formula. Unfortunately i didn't take into account empty cells in my previous suggestion.

@Quadruple_Pawn  alternatively



note: Both of these formulas will count extra points if someone can put the same country in multiple times.  For example Brazil, Brazil, Brazil would get 5 points (3 for correct in correct place + 1 + 1 for correct in wrong place).