Concentrating on solving the immediate problems I lost sight of the bigger picture. If you use a function to do this calculation then everytime that you change anything in the data range the function is executed, and as it scans through a lot of cells it is not the quickest, now that you also want to calculate the total number of penalties , so to function executions.
I would do both calculations in one macro assigned to a command button on the profile sheet and just run the macro when required.
I've amended the code , this places the total penalties in the column next to the individual, but you will probably want to change that.
Brian
Code:
Sub mysub()
Dim rng As Range
Dim pentot As Long, allpen As Long
pentot = 0
allpen = 0
For Each rng In Range("ALLGoals")
If rng.Interior.ColorIndex = 35 Then
allpen = allpen + 1
If rng.Value = Range("filename").Value Then
pentot = pentot + 1
End If
End If
Next rng
Range("Penalties").Offset(0, 1) = allpen
Range("Penalties") = pentot
End Sub