How to not duplicate countif text within cells?

Thanks Marcol - it's pretty close (see attached sheet). It's definitely only counting once per cell as cell B3, which contains all 3 names is only counted once.

However, there's a bit of a glitch in that every "Yes" in column A will be counted, regardless of what's in columnB. I'm very new to sumproduct so I'm not sure if that's a glitch in the equation or what...

Thank you!