I tried to include SUMIFS with single array and it works fine:
=SUM(SUMIFS(Sheet3!H:H,Sheet3!G:G,"X",Sheet3!E:E,"SA",Sheet3!D:D,{"A","B","C"}))
When I try to include a second array as below it gives me only the first/last item from {"X","Y","Z"}
=SUM(SUMIFS(Sheet3!H:H,Sheet3!G:G,{"X","Y","Z"},Sheet3!E:E,"SA",Sheet3!D:D,{"A","B","C"}))
The only workaround I could figure out is by separating the formula in 3 chunks (each item in {"X","Y","Z"}).
Is there any other work around? Or two arrays in SUMIFS does not work properly?
You can use a row array for one of the lists and a column array for the other. Try this:
=SUM(SUMIFS(Sheet3!H:H, Sheet3!G:G, {"X","Y","Z"},
Sheet3!E:E,"SA",Sheet3!D:D,{"A";"B";"C"}))
' ^ ^
The inner SUMIFS makes a cross-product of the two arrays; a matrix where each entry is the "sumif" matching both the corresponding row and column elements:
X,A Y,A Z,A
X,B Y,B Z,B
X,C Y,C Z,C
Then the outer SUM adds up all the elements. As a result, you will have the sum where each field matches any element of it's criteria's array:
G:G is any of {"X","Y","Z"} and D:D is any of {"A";"B";"C"}
Of course the other, single condition E:E="SA" applies in all cases.
The two lists don't need to have the same cardinality.
This technique cannot be generalized to more than two lists. If you had a third criteria list, you need to proceed in a different way.