title: sumifs with 3 or more multiple array criteria
body: <p>I have a <code>SUMIFS</code> 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).   </p>
  
  <p>Examples online show you can achieve a sumifs with OR criteria by doing the following:</p>
  
  <pre><code>{=SUM(SUMIFS(sumrange,
      criteriarange0,"x",
      criteriarange1,{"1","2","3"},
      criteriarange2,{"A";"B"}
  ))}
  </code></pre>
  
  <p>where<br>
  <code>"x"</code> is the value in drop-down,<br>
  <code>{"1","2","3"}</code> are the resulting values if the three corresponding check boxes are ticked,<br>
  <code>{"A";"B"}</code> are another set of resulting values if their linked check boxes are ticked.<br>
  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). </p>
  
  <p>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:</p>
  
  <pre><code>{=SUM(SUMIFS(sumrange, 
      criteriarange0,"x", 
      criteriarange1,value_range1, 
      criteriarange2,value_range2, 
      criteriarange3,value_range3
  ))}
  </code></pre>
  
  <p>I've found something involving <code>SUMPRODUCT</code> and <code>ISNUMBER(MATCH()</code> 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.</p>
  
  <p>Here is the crude formula I currently have with annotations:</p>
  
  <pre><code>={SUM(
  
  SUMIFS(sumrange,$A:$A, $A1, $B:$B, $B1, $C:$C, $C1...,  
  </code></pre>
  
  <blockquote>
    <p>'sum a range conditional on the values in cells $A1, $B1, $C1. These are AND criteria.</p>
  </blockquote>
  
  <pre><code>$E:$E,$AA10:$AC10, 
  </code></pre>
  
  <blockquote>
    <p>'include the dynamic values in $AA10:$AC10, which have the values: 5 years, 10 years, 15 years. These are OR criteria.  </p>
  </blockquote>
  
  <pre><code>$F:$F,{$AA11;$AC11}, 
  </code></pre>
  
  <blockquote>
    <p>'also include these dynamic values for a different cell range: "5 days, 10 days, 15 days". These are OR criteria.</p>
  </blockquote>
  
  <pre><code>$G:$G,{$AA12;$AJ12})
  )} 
  </code></pre>
  
  <blockquote>
    <p>'as well as include these multiple dynamic values, which are also OR criteria: "5 apples, 10 pears, 15 bananas".  </p>
    
    <p>Any combination of the dynamic values can be chosen.</p>
  </blockquote>