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 LongErase Total()For X = 6 To 31 Step 1 'This is the rows starting at 6For Y = 3 To 40 Step 1 'This is the columns starting at CTotal(1) = Total(1) + Left(Cells(X, Y), 1) 'StartedTotal(2) = Total(2) + Mid(Cells(X, Y), 3, 3) 'Time PlayedTotal(3) = Total(3) + Mid(Cells(X, Y), 7, 1) 'Yellow CardsTotal(4) = Total(4) + Mid(Cells(X, Y), 9, 1) 'Red CardsTotal(5) = Total(5) + Mid(Cells(X, Y), 11, 1) 'InjuredTotal(6) = Total(6) + Mid(Cells(X, Y), 13, 1) ' AssistsTotal(7) = Total(7) + Mid(Cells(X, Y), 15, 1) 'GoalsNext Y 'Progress to next column in row'This outputs the totals in column 39 (AO)For i = 1 To 7 Step 1If i < 7 ThenCells(X, 39) = Cells(X, 39) & Total(i) & "-"ElseCells(X, 39) = Cells(X, 39) & Total(i)End IfNext iErase Total()Next X 'Progress to next playerEnd Sub