Excel SUMIFS - Multiple Array

Question

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're right. I omitted it accidentally while editing. I've updated it now — user3168017
– user3168017, Commented Jun 9, 2017 at 1:22

A.S.H · Accepted Answer · 2017-06-09 13:00:42Z

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.

Collectives™ on Stack Overflow

Excel SUMIFS - Multiple Array

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related