VBA - count by multiple criteria and font colour

Hello

I am trying to write a function that will count by multiple criteria and font colour.
I basically want to be able to determine the number of human errors per person whilst omitting names in red.
I have a column of names (those with failing result) and a column of causes.
I realise I can use the countifs function for the names and human error but I can't omit those in red.
I can write a function that will count how many times a name appears in a range, omitting the red ones. How can I merge these?
I have also written a function that will count human errors in a given range. I just can't seem to make it count how many human errors per person for a given range!
Out of interest I'd like to work out the VBA code for this, as well as actually finding a solution.

Example attached

When you enter the function in the cell, which cells should be selected in the range and what should be in criteria1?

So I'm selecting the names for a given week and the criteria is the first name on the second sheet. And then I iterate through changing criteria1 to each name.
E.g Week 1: I select the names for that week (B2:B14) and then the Criteria1 iterates from Sheet2 A2:15. Does that make sense?

It's the merging I'm struggling with!

Here's how I read your request.
UDF
Formula in Sheet2!B2
=CountName(Sheet1!$A$2:$C$40,B$1,$A2,3)
then copy right + down

Function CountName(rng As Range, myWeek, myName, myColorIndex As Long) As Long
    Dim r As Range, x, e
    Application.Volatile
    myWeek = Val(Split(myWeek & " ")(1))
    With rng
        x = Filter(.Parent.Evaluate("transpose(if((" & .Columns(1).Address & "=" & myWeek & ")*(" & _
            .Columns(2).Address & "=""" & myName & """)*(" & .Columns(3).Address & _
            "<>""""),row(1:" & .Rows.Count & ")))"), False, 0)
        If UBound(x) > -1 Then
            For Each e In x
                CountName = CountName + Abs(rng.Cells(e, 2).Font.ColorIndex <> myColorIndex)
            Next
        End If
    End With
End Function

Ah yes that works! Do you think you could explain it to me a bit?

1)

    Application.Volatile

This enable to use F9 to recalculate since Change of Font/Interior color cell format will not trigger to recalculate.
2)

    myWeek = Val(Split(myWeek & " ")(1))

Assuming the referenced cell contains the value like "Week #" or just "x #" and it reads "#" part
3)

        x = Filter(.Parent.Evaluate("transpose(if((" & .Columns(1).Address & "=" & myWeek & ")*(" & _
            .Columns(2).Address & "=""" & myName & """)*(" & .Columns(3).Address & _
            "<>""""),row(1:" & .Rows.Count & ")))"), False, 0)

Extract the row reference within rng that is/are 1st column matches "#" part, AND 2nd column matches to Name AND 3rd column is not empty.
4)

        If UBound(x) > -1 Then
            For Each e In x
                CountName = CountName + Abs(rng.Cells(e, 2).Font.ColorIndex <> myColorIndex)
            Next
        End If

Loop though the result and it adds to CountName when font colorindex of 2nd column of that row is not 3.

Do a step debug for yourself first.