In terms of modifying the SUMIF(S) approach:
=SUM(SUMIFS(Properties!$CH$3:$CH$1002,Properties!$S$3:$S$1002,{"Y","Y","A","A"},Properties!$V$3:$V$1002,{"Y","A","Y","A"}))/MAX(1,SUM(COUNTIFS(Properties!$S$3:$S$1002,{"Y","Y","A","A"},Properties!$V$3:$V$1002,{"Y","A","Y","A"},Properties!$CH$3:$CH$1002,"<>")))
else, in Array terms
=IFERROR(AVERAGE(IF(ISNUMBER(MATCH(Properties!$S$3:$S$1002&"^"&Properties!$V$3:$V$1002,{"A^Y","A^A","Y^A","Y^Y"},0))*(Properties!$CH$3:$CH$1002<>""),Properties!$CH$3:$CH$1002)),0)
confirmed with CTRL + SHIFT + ENTER (enter alone will not suffice)
Bookmarks