Another option for 2nd part.
In C19, copy across
=0+(SUM(COUNTIF(OFFSET(C21,{0,3},,2),"Yes"))>0)
And,
If you wanted to search for "Yes" in C4,C6,C7,C8,C10 (as per post #3)
try :
=0+(SUM(COUNTIF(OFFSET(C4,{0,2,3,4,6},),"Yes"))>0)
Formula copy across
Regards
Bosco