sumifs with 3 or more multiple array criteria

Question

I have a SUMIFS that works off values contained in drop-downs and check boxes. The values in the drop-downs work as AND criteria (i.e. sum a column conditional on this value in the drop down AND that value in the drop down), but things get tricky when I want to include the values in the check boxes, which are dynamic based on user entry. Essentially the values in the check box need to work as OR criteria (i.e. sum a column based on this value if box 1 is selected OR that value if box 2 is selected, also conditional on the values in the drop-down).

Examples online show you can achieve a sumifs with OR criteria by doing the following:

{=SUM(SUMIFS(sumrange,
    criteriarange0,"x",
    criteriarange1,{"1","2","3"},
    criteriarange2,{"A";"B"}
))}

where
"x" is the value in drop-down,
{"1","2","3"} are the resulting values if the three corresponding check boxes are ticked,
{"A";"B"} are another set of resulting values if their linked check boxes are ticked.
1,2,3,A,B are all Boolean, in that they can change from the value to 0 if the check box is not ticked (so there are a matrix of combinations).

My question is how do you do this with 3 or more multiple arrays that act as OR conditions, which are quoted in the formula as cell references? Something like this:

{=SUM(SUMIFS(sumrange, 
    criteriarange0,"x", 
    criteriarange1,value_range1, 
    criteriarange2,value_range2, 
    criteriarange3,value_range3
))}

I've found something involving SUMPRODUCT and ISNUMBER(MATCH() formulae, but can't get it to work. Additionally the table is 500,000 rows x 100 columns, so the formula needs to be efficient.

Here is the crude formula I currently have with annotations:

={SUM(

SUMIFS(sumrange,$A:$A, $A1, $B:$B, $B1, $C:$C, $C1...,

'sum a range conditional on the values in cells $A1, $B1, $C1. These are AND criteria.

$E:$E,$AA10:$AC10,

'include the dynamic values in $AA10:$AC10, which have the values: 5 years, 10 years, 15 years. These are OR criteria.

$F:$F,{$AA11;$AC11},

'also include these dynamic values for a different cell range: "5 days, 10 days, 15 days". These are OR criteria.

$G:$G,{$AA12;$AJ12})
)}

'as well as include these multiple dynamic values, which are also OR criteria: "5 apples, 10 pears, 15 bananas".

Any combination of the dynamic values can be chosen.

You can't, you will need to do multiple sumifs with each of the third criteria and add them together. — Scott Craner

Unknown Unknown · Accepted Answer · 2018-12-07T17:41:22

You can sum the totals produced by two individual SUMIFS representing the last criteria.

=SUM(SUM(SUMIFS(A:A, B:B, "x", C:C, {1,2,3}, D:D, "a")), 
     SUM(SUMIFS(A:A, B:B, "x", C:C, {1,2,3}, D:D, "b")))

The last two criteria cannot be used in the same layout as the second criteria. This produces confusion and miscalculation during the array's cyclic calculation. However, if you can have the last criteria thought of as being in a different layout as the second criteria the calculation cycles should produce the correct result. TRANSPOSE will change the 'direction' of the last criteria array, thus changing the way it is calculated.

=SUM(SUMIFS(A:A, B:B, "x", C:C, {1,2,3}, D:D, TRANSPOSE({"a","b"})))

This produces the same result¹ as the first formula.

To add a third array of OR-based criteria, you would need to combine the two methods.

=SUM(SUM(SUMIFS(A:A, B:B, "x", C:C, {1,2,3}, D:D, TRANSPOSE({"a","b"}), E:E, "m")),
     SUM(SUMIFS(A:A, B:B, "x", C:C, {1,2,3}, D:D, TRANSPOSE({"a","b"}), E:E, "n")),
     SUM(SUMIFS(A:A, B:B, "x", C:C, {1,2,3}, D:D, TRANSPOSE({"a","b"}), E:E, "o")))

¹ _{Sample data:}
_{Column A filled with random numbers.
    Column B filled with =CHAR(RANDBETWEEN(87, 89))
    Column C filled with =RANDBETWEEN(0, 5)
    Column D filled with =CHAR(RANDBETWEEN(65, 67))}

sumifs with 3 or more multiple array criteria

1 Answers