Ok, then try this
I think you were getting FALSE instead of the expected "No" because the first IF didn't have a value for a false condition, and so it defaulted to just False
Row 4: =IF(D4="M",IF(SUM('4'!$O$32:$O$42)>=5,"Yes",IF('4'!$O$32>=1,"Yes","No")), "No")
Row 5: =IF(D5="M",IF(SUM('5'!$O$32:$O$42)>=5,"Yes",IF('5'!$O$32>=1,"Yes","No")), "No")
Row 6: =IF(D6="M",IF(SUM('6'!$O$32:$O$42)>=5,"Yes",IF('6'!$O$32>=1,"Yes","No")), "No")
I'd suggest that you look into using INDIRECT() and ROW() to avoid a lot of typing where the only different in 46 rows it the referenced sheet name