I would like to include the count of blank cells in a column using countifs
=IF(COUNTIFS(Table5[CH Code & Room],$U67,Table5[Residency from],"<="&AA$6,Table5[Residency to],">="&AA$6,Table5[Residency to],"")>=1,1,0)
This formula works will all apart from counting blanks
All the -IFS functions use AND logic so, if you want to use OR logic then you would have to sum multiple -IFS functions - a more concise approach is to use SUMPRODUCT(), e.g.
=IF(SUMPRODUCT((Table5[CH Code & Room]=$U67)*(Table5[Residency from]<=AA$6)*((Table5[Residency to]>=AA$6)+(Table5[Residency to]=""))),1,0)