COUNTIFS('Orange'!I1:I1466,"Smith...",'Pink'!H1:H1466,"office")

This is the formula that is being used. I also need this formula to omit or subtract any cell in column E1:E466 with an "r" in it.

Example: I need a formula that looks at all this criteria but only counts if there is not an "r" or subtracts if there is an "r".

Smith… office r
Johnson.. office
Davis office r

So my results would look like this:

Orange
Smith 0
Johnson 1
Davis 0