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

Leave a Reply

Your email address will not be published. Required fields are marked *