arraysexcelexcel-formulasumifs

SUMIFS with multiple arrays or the "OR" listing

Would like to create a excel sumifs function, which taking arrays / listing as input for multiple criteria. Currently using 2019 version.

Did some research and thought of using sumifs(sum_range,criteria_range,{x;y},...) or sumifs(sum_range,criteria_range,{x,y},...).

Example, was essentially expecting all formulas to generate result of 4.

enter image description here

  1. To understand the difference between {x;y} & {x,y} in sumifs formula

  2. What cause the difference in results using different formula

  3. Any excel formula out there that I could use for multiple criteria sumifs, and all requiring arrays/listing as input. (For example, summing up column A, with column B having XX or YY as input, and column C having ZZ or WW as input, and column D having UU or TT or UT as input Was thinking something below, but it does not work:

    sumifs(A1:A100,B1:B100,{"XX","YY"},C1:C100,{"ZZ","WW"},D1:D100,{"UU","TT","UT"})

From research I could do like sumifs(A1:A100,B1:B100,{"XX","YY"},C1:C100,TRANSPOSE({"ZZ","WW"})), but could not find any answer for 3 or more criteria.

Solution

  • You can't use more than two arrays with that syntax. Since you don't have FILTER, the simplest option to my mind is to use a helper column with a formula like:

    =AND(OR(B1={"XX","YY"}),OR(C1={"ZZ","WW"}),OR(D1={"UU","TT","UT"}))

    which will return TRUE/FALSE for each row. Then simply use this column as a single criterion column in a SUMIF formula.