# Summing MID Function Results in One Cell 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?

### One thought on “Summing MID Function Results in One Cell”

1. andrew walker Post author

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`