Count with colour combination formula

[crazymaca69]

Hi guys,

I have a request that may be quite hard to explain but here goes:

The spreadsheet attached holds a list of names, their IDs and their location (Whether or not they are due to leave the job)

The top row has competencies that, for each person are formatted yellow for desired, pink for essential and white for not required.

I have a countcolour in D7 that is counting how many people require the competence (essential)
In D8, I have a countcolour that determines which people hold the competence (but only the essential ones) I don't care about people who have it but don't really need it.
In D9 I have a countcolour that determines which people have it booked.
In D10, I have a COUNTIFS that determines how many people are due to leave who hold the Q or have it booked. (set to <> for anything in the cell)

The problem is that this that D10 accounts for all people, not just those who have it as an essential Q.
I was trying to combine the countcolour with the COUNTIFS formula but this doesn't seem to be possible.

I'm guessing i'll need to add another module but have no VBA experience.

Any help appreciated!

Mark

[crazymaca69]

Can anybody help with this?

[Glenn Kennedy]

What should the answer be... and why??

[crazymaca69]

what do you mean?

[Glenn Kennedy]

What is tje manually calculated correct answer? How was it derived?

[FDibbins]

1st, you dont really need SUM in that formula.
2nd, if I understand you correctly, maybe this?
=SUMPRODUCT(--(D3:D6=E13:I13)*(C3:C6<>"due to leave"))