Group scores based on value in first column

Hello,

I used 2 SUMPRODUCT to do the job.

See if the attached file is what you are looking for.

P/s: Your expected results in some place is not accurate by the way.

Thanks for the feedback. Yes you are right, from C to I is enough.

I have a habit of adding extra column so the references match each other (same number of column for both reference on SUMIF).

Anyway, I'm glad that I could help. Let me know if you still have any trouble.

Have a nice day.

For the first SUMPRODUCT, I used it to calculate the number of time a player participated in a tournament, for example, this is the one in Cell C18

Please Login or Register  to view this content.

What it does is combine value in A4 to A11 with B4 to H11 and create an array of player + tournament name (for e.g "AnnaTournament 1", "BertTournament 2", and so on), then compare it with the player name and tournament you are checking (on Cell C18, it's "AnnaTournament 1"). The "--" added in front of it makes the logic statement return 1 or 0 instead of TRUE or FALSE, thus making them sum-able. If this SUMPRODUCT returns 0, obviously Anna does not participated in Tournament 1, right?

With the same idea, I used the second SUMPRODUCT to calculate the point

Please Login or Register  to view this content.

It's basically the same, with the added reference from C4 to I11, is where the points are. When the reference of player combined with a tournament name is found on a specific row / column, it will return the corresponding value of points in the same row but in the column next to it (that's why for the first part of the Cell Reference is from B4 to H11, but the later part is C4 to I11. Also, this is why I keep the habit of adding extra column to keep the reference match).

I'm sorry if I have confused you because I am not that good with explanation. What you can always do is to copy straight one of the SUMPRODUCT on the formula, paste it else where to see what it's doing, and try changing the reference.