Okay, so I have a spreadsheet as shown above. I want to capture the statistics for my football team’s individual players for each game. The format is “Started-Time Played-Yellow Cards-Red Cards-Injured-Assists-Goals”.
At the end of the season there will be 38 cells, each containing a string 15 characters long, for each player.
I want to compile statistics as a running total for each player for instance Time Played.
To do this for Podolski and goals scored I can use the following formula:
=MID(C25,15,1)+MID(D25,15,1)+MID(E25,15,1)+………+MID(AN25,15,1)
I can use similar formulas depending on what statistic I want to calculate.
Trouble is this will result in a lot of long, unwieldy formulas.
Is there an easier way to do this?
Essentially, I want to perform the MID function on multiple strings in different cells and sum them, all in one cell.
Is there any way to do this without using VB?
Hi
I think on this occasion VBA might be the better option…
I tested the below code on my machine. You will need to insert this into a new module. REMEMBER to back up your file first before testing the macro just in case something goes wrong!
Sub Player_Stats()
Dim Total(7) As Long
Erase Total()
For X = 6 To 31 Step 1 'This is the rows starting at 6
For Y = 3 To 40 Step 1 'This is the columns starting at C
Total(1) = Total(1) + Left(Cells(X, Y), 1) 'Started
Total(2) = Total(2) + Mid(Cells(X, Y), 3, 3) 'Time Played
Total(3) = Total(3) + Mid(Cells(X, Y), 7, 1) 'Yellow Cards
Total(4) = Total(4) + Mid(Cells(X, Y), 9, 1) 'Red Cards
Total(5) = Total(5) + Mid(Cells(X, Y), 11, 1) 'Injured
Total(6) = Total(6) + Mid(Cells(X, Y), 13, 1) ' Assists
Total(7) = Total(7) + Mid(Cells(X, Y), 15, 1) 'Goals
Next Y 'Progress to next column in row
'This outputs the totals in column 39 (AO)
For i = 1 To 7 Step 1
If i < 7 Then
Cells(X, 39) = Cells(X, 39) & Total(i) & "-"
Else
Cells(X, 39) = Cells(X, 39) & Total(i)
End If
Next i
Erase Total()
Next X 'Progress to next player
End Sub