Counting Number of Vacant Units

In the attached I put this formula in Sheet2 E36:E47. It returns the percentages directly from Sheet1

Q39:Q50. It saves having to jump across sheets / ranges to find / verify the percentages as you can see

the percentages in the next column to the right. It seemed easier.

Formula:

=INDEX(Sheet1!$P$39:$Q$50,MATCH($A36,Sheet1!$P$39:$P$50,0),2)

If I interpret your intent from your existing formulas correctly the count of vacancies depends solely

upon the number of blank cells in Sheet1 B10:B12. With that understanding I put this formula in

D36:D47.

Formula:

=IF($E36=100%,0,COUNTIF(Sheet1!$B$10:$B$12,""))

I then deleted some Tenant cells data in B10:B12 to test it.

Does this help?