Still not sure what you're trying to do, but maybe this. In B18 and copy down and across,

=IF($B$1 <> $A18, "", COUNTIF(A$4:A$9, "Yes") / COUNTA(A4:A9) )