• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

SUMIFS - Multiple Array

A

AV114

Member
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?
 
Hi,

Just replace the comma with semi colon in second array:

=SUM(SUMIFS(Sheet3!H:H,Sheet3!G:G,{"X","Y","Z"},Sheet3!E:E,"SA",Sheet3!D:D,{"A";"B";"C"}))

Regards,
 
Hi,

Beyond two sets of array criteria, you generally need to switch to SUMPRODUCT, e.g. (assuming the third array criterion is for column F to contain either "D" or "E"):

=SUMPRODUCT(Sheet3!H1:H25,0+ISNUMBER(MATCH(Sheet3!G1:G25,{"X","Y","Z"},0)),0+(Sheet3!E1:E25="SA"),0+ISNUMBER(MATCH(Sheet3!D1:D25,{"A";"B";"C"},0)),0+ISNUMBER(MATCH(Sheet3!F1:F25,{"D";"E"},0)))

The main drawback to switching to SUMPRODUCT is that referencing entire columns would now be strongly inadvisable (hence my choice of 25 for the upper row reference).

If the array criteria are reasonably small then you can still create the necessary SUMIFS construction, though not without some convolution, e.g.:

=SUM(SUMIFS(Sheet3!H:H,Sheet3!G:G,{"X","X","X","Y","Y","Y","Z","Z","Z"},Sheet3!E:E,"SA",Sheet3!D:D,{"A","B","C","A","B","C","A","B","C"},Sheet3!F:F,{"D";"E"}))

However, the ability to reference entire columns most likely does not compensate for the additional computation required.

Regards
 
You must log in or register to reply here.
Back
Top