Countifs over multiple columns and rows

[howe7032]

I'm trying to execute a COUNTIFS formula where the criteria range is across multiple columns and rows, but I keep getting #VALUE! returned in the cell. Here is the formula I'd like to have:

=COUNTIFS(Sheet3!$S:$S,Sheet1!$A$5,Sheet3!AF:AJ,Sheet2!$A4)

I run into trouble with my criteria_range2. Instead of being able to list AF:AJ, I'm having to create separate formulas for each row (AF:AF, AG:AG, etc). I have tried AF1:AJ10 as well - no luck. So my formula ends up as:

=COUNTIFS(Sheet3!$S:$S,Sheet1!$A$5,Sheet3!AF:AF,Sheet2!$A4), then =COUNTIFS(Sheet3!$S:$S,Sheet1!$A$5,Sheet3!AG:AG,Sheet2!$A4) in the next column, etc, etc.

I've attached a sample. This sample is a simplified version of what I'm really using (I can't send it out due to privacy issues) and the one I'm working with is actually much larger ranges (AD:AR and over 700 rows of data).

Any suggestions?

Thanks.

[Jonmo1]

Try

=SUMPRODUCT((Sheet3!$S$2:$S$100=Sheet1!$A$5)*(Sheet3!$AF$2:$AJ$100=Sheet2!$A3))