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.
Bookmarks