CODEWITHSUNDEEP
arraysexcel-formulasumifs
user3168017
user3168017

Reputation: 39

Excel SUMIFS - Multiple Array

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?

Upvotes: 1

Views: 8930

Answers (1)

A.S.H
A.S.H

Reputation: 29332

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.

Upvotes: 3

Related Questions