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?

## You might also find helpful in Excel...

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`