Football (Soccer) Excel Formula

[Hypnoidz]

Just asking for a bit of help and if anyone is up for maybe a challenge.

I have a 6 a-side football (soccer) group and wanted to keep track of points for a league that we do.

I preferably would like to keep a list of the players names (roughly 14 ppl). If a player is on the winning team they will earn 3 points, a draw 1 point and a loss will be 0 points.

We also have a captain and captain bonus system. If a player is a captain and wins a match they will earn an extra point along with the 3 points for the win (4 points total). If its a loss the captain will - 1 point. We have "bonus" points also where if you win a match as a captain you would earn 2 extra points along with the 3 points for the win (5 points total). If its a loss the captain will - 2 points.

For example:
Team 1: Liam (Captain), Jack and Stu
Team 2: Shane (Captain), Sean and James

Team 1 wins and both captains used a double captain. In the standings it would be Liam (5 points), Jack (3 points), Stu (3 points). Shane (-2 points), Sean (0 points), James (0 points).

I'll post a link. Don't know if it'll work:

Book.xlsx

Microsoft Excel Workbook

1drv.ms

Thanks all!

 

[I_know_nuffin]

if you add your team names to Row 2 starting at Column H

then in H3 add the below and drag across all names and down all games

=(IFERROR(IF(FIND(H$2,$B3)>0,3,0),0) ) + (IFERROR( IF(FIND(H$2&" (C)",$B3)> 0,1,0),0)) + (IFERROR(IF(FIND(H$2,$D3)>0,1,0),0)) + (IFERROR(IF(FIND(H$2&" (C)",$C3)>0,-1,0),0)) + (IFERROR(IF( AND(FIND(H$2,$D3)>0,FIND(H$2&" (C)",$C3)>0),-2,0),0))

I haven't added a draw option as the example sheet didn't show any draws

basically it looks for the player name in the win / lose columns and the double captain column and adds the points together

FIND(H$2,$B3)>0 find the player name from H2 in B3
IF(FIND(H$2,$B3)>0,3,0) if its found return 3 points or return 0 points
(IFERROR(IF(FIND(H$2,$B3)>0,3,0),0) if it cant find the player it causes an error so IFERROR returns 0 points

IF(FIND(H$2&" (C)",$B3)> 0,1,0) looks for player name and the (C) to find the captain and return 1 point

 

[I_know_nuffin]

if you add your team names to Row 2 starting at Column H

should read

if you add your player names to Row 2 starting at Column H