I would like to use a formula like COUNTIF or SUMPRODUCT to test two ranges.
I want the formula to return the value of the number of cells that meet the
two conditions. Both columns contain TEXT, not numbers.

Like this:
=COUNTIF(--(Sheet1!B:B, "Move-In"),--(Sheet1!E:E,"A1"))

So if there are 10 Move-Ins with Type A1, then the value 10 would be
calulated.