VBA code to count certain criteria

[Lensmeister]

Tight I'll change that when I get home.

You are a star Brian

I will adapt it just to count the colus to giver an overall stat of all the pens scored.

 

[Brianwarnock]

Oh dear! Oh dear!

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

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