SUM and COUNTIFS with multiple arrays?

Question

I am trying to obtain a count of rows where specific criteria are met. I have searched on SO for an answer to this, but have been unable to locate a solution thus far (NOTE: I have found solutions to using SUM and COUNTIFS with one array, but not multiple).

Here is the formula I am using:

=SUM(COUNTIFS('Sheet1'!$AA:$AA,"<="&$B1,'Sheet1'!$AE:$AE,"="&"",'Sheet1'!$Q:$Q,"<>"&{"Value1","Value2","Value3","Value4"},'Sheet1'!$S:$S,"<>"&{"ValueA","Value B","ValueC","ValueD","ValueE"}))

The data is customer case data. Cell B1 is a week ending date. Column AA in Sheet 1 is the Case Open Date, and Column AE is the Case Closed Date. Column Q and S are filter criteria.

The expected count returned is a count of cases that were opened during the current week or earlier (Criteria 1) that are still open (Criteria 2) where the case Category (Column Q) is not one of the values in Array 1 (Criteria 3) and the case Type is not one of the values in Array 2 (Criteria 4). The result being returned is higher than expected (this was validated by do a manual filtering on the data set using these same criteria).

My guess is that double counting is going on, since the formula returns 1,828 cases for one week tested but manually filtered validation shows 1,241 (the expected result). Any thoughts or ideas on this is much appreciated.

Answer 1

The problem is that the arrayed count against the Value1, Value2, Value3, Value4 is being treated as an OR condition, not an AND condition. Even if Q2 has Value1 in it, it doesn't have Value2, Value3 or Value4 so the count is coming back as true. You need to make sure that Q2 has none of Value1, Value2, Value3, Value4 in it. The same goes for column S and ValueA, ValueB, ValueC, ValueD. This OR behavior works well when you are trying to see in the values are included but not when they are intended to be excluded.

    

An old-fashioned SUMPRODUCT function can handle the OR conditions.

        

The formula with array constants in B2 is,

=SUMPRODUCT((Sheet1!$AA$2:$AA$999<=$B1)*SIGN(Sheet1!$AA$2:$AA$999)*(Sheet1!$AE$2:$AE$999="")*ISERROR(MATCH(Sheet1!$Q$2:$Q$999, {"Value1","Value2","Value3","Value4"}, 0))*ISERROR(MATCH(Sheet1!$S$2:$S$999, {"ValueA","Value B","ValueC","ValueD","ValueE"}, 0)))

The alternate formula in B3 using the values from the ranges in D4:D7 and E4:E8 is,

=SUMPRODUCT((Sheet1!$AA$2:$AA$999<=$B1)*SIGN(Sheet1!$AA$2:$AA$999)*(Sheet1!$AE$2:$AE$999="")*ISERROR(MATCH(Sheet1!$Q$2:$Q$999, $D$4:$D$7, 0))*ISERROR(MATCH(Sheet1!$S$2:$S$999, $E$4:$E$8, 0)))