VBA - count by multiple criteria and font colour

[nanotek]

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.

[nanotek]

Example attached

[nightseeker]

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

[nanotek]

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?

[nightseeker]

Try this out. Note that this doesn't include your range 2. Maybe with this you might be able to merge your 2 functions together.

[jindon]

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

Please Login or Register  to view this content.

[nanotek]

It's the merging I'm struggling with!

[nanotek]

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

[jindon]

1)

Please Login or Register  to view this content.

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

Please Login or Register  to view this content.

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

Please Login or Register  to view this content.

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)

Please Login or Register  to view this content.

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.