SUMPRODUCT/SUMIFS Dynamic array doesn't works

[regoroes]

Dear all,

Original Formula:
SUMPRODUCT(SUMIFS(Sales!$H:$H,Sales!$E:$E,OFFSET('Product Portfolio'!$A$1,MATCH($M14,'Product Portfolio'!$A$1:$A$918,0)-1,1,COUNTIF('Product Portfolio'!$A$1:$A$918,$M14),1),Sales!$D:$D,$H14,Sales!B:B,$K14,Sales!$F:$F,$N14))


Revised Formula:
=SUMPRODUCT(SUMIFS(Sales!$H:$H,Sales!$E:$E,OFFSET('Product Portfolio'!$A$1,MATCH($M16,'Product Portfolio'!$A$1:$A$918,0)-1,1,COUNTIF('Product Portfolio'!$A$1:$A$918,$M16),1),Sales!$D:$D,$H16,Sales!B:B,$K16,Sales!$F:$F,OFFSET('Channel Sub Channel'!$A$1,MATCH($N16,'Channel Sub Channel'!$A$1:$A$918,0)-1,1,COUNTIF('Channel Sub Channel'!$A$1:$A$918,$N16),1)))

The original formula works. Howeve, after I changed the last SUMIF criteria for Sales!$F:$F with an additional dynamic range, the formula doesn't works and return zero.

Can anyone help please?

 

[yosegmail]

post some data you use

 

[regoroes]

Dear yosegmail,

Product Portfolio

Product ID	Product
Fruits	Apple
Fruits	Pear
Fruits	Orange
Vegetable	Carrot
Vegetable	Capsicum

<colgroup><col><col></colgroup><tbody>
</tbody>

Channel Sub Channel

Channel ID	Channel
GH	GH
GP+PH	GP
GP+PH	PH
GP+PH+PY	GP
GP+PH+PY	PH
GP+PH+PY	PY
PH	PH
PY	PY

<colgroup><col><col></colgroup><tbody>
</tbody>

Sales

Product	Channel	Sales
Apple	GP	100
Pear	PH	200
Orange	PY	300
Pear	GP	200
Apple	PH	100
Orange	PY	300

<colgroup><col width="69" span="3" style="width:52pt"> </colgroup><tbody>
</tbody>

Channel	Portfolio	Total Sales
GP+PH+PY	Fruits	1200

<colgroup><col><col span="2"></colgroup><tbody>
</tbody>

Tried using this formula, but doesn't works..

=SUMPRODUCT(SUMIFS(Sales!C:C,Sales!A:A,OFFSET('Product Portfolio'!$A$1,MATCH($B2,'Product Portfolio'!$A$1:$A$918,0)-1,1,COUNTIF('Product Portfolio'!$A$1:$A$918,$B2),1),Sales!B:B,OFFSET('Channel Sub Channel'!$A$1,MATCH($A2,'Channel Sub Channel'!$A$1:$A$918,0)-1,1,COUNTIF('Channel Sub Channel'!$A$1:$A$918,$A2),1)))

 

[barry houdini]

It's OK to have one "list" as a criteria in SUMIFS but if you have two then one has to be a "row" and one a "column", so you need to transpose one of the ranges. Try this "array formula"

=SUM(SUMIFS(Sales!$H:$H,Sales!$E:$E,OFFSET('Product Portfolio'!$A$1,MATCH($M16,'Product Portfolio'!$A$1:$A$918,0)-1,1,COUNTIF('Product Portfolio'!$A$1:$A$918,$M16),1),Sales!$D:$D,$H16,Sales!B:B,$K16,Sales!$F:$F,TRANSPOSE(OFFSET('Channel Sub Channel'!$A$1,MATCH($N16,'Channel Sub Channel'!$A$1:$A$918,0)-1,1,COUNTIF('Channel Sub Channel'!$A$1:$A$918,$N16),1))))

confirm with CTRL+SHIFT+ENTER

 

[regoroes]

It's OK to have one "list" as a criteria in SUMIFS but if you have two then one has to be a "row" and one a "column", so you need to transpose one of the ranges. Try this "array formula"

=SUM(SUMIFS(Sales!$H:$H,Sales!$E:$E,OFFSET('Product Portfolio'!$A$1,MATCH($M16,'Product Portfolio'!$A$1:$A$918,0)-1,1,COUNTIF('Product Portfolio'!$A$1:$A$918,$M16),1),Sales!$D:$D,$H16,Sales!B:B,$K16,Sales!$F:$F,TRANSPOSE(OFFSET('Channel Sub Channel'!$A$1,MATCH($N16,'Channel Sub Channel'!$A$1:$A$918,0)-1,1,COUNTIF('Channel Sub Channel'!$A$1:$A$918,$N16),1))))

confirm with CTRL+SHIFT+ENTER

Dear Barry,

Thank you so much! Would you be able to kindly help me understand how this formula works?

Are we able to have more than 2 "lists" of arrays as criteria?

 

[regoroes]

It's OK to have one "list" as a criteria in SUMIFS but if you have two then one has to be a "row" and one a "column", so you need to transpose one of the ranges. Try this "array formula"

=SUM(SUMIFS(Sales!$H:$H,Sales!$E:$E,OFFSET('Product Portfolio'!$A$1,MATCH($M16,'Product Portfolio'!$A$1:$A$918,0)-1,1,COUNTIF('Product Portfolio'!$A$1:$A$918,$M16),1),Sales!$D:$D,$H16,Sales!B:B,$K16,Sales!$F:$F,TRANSPOSE(OFFSET('Channel Sub Channel'!$A$1,MATCH($N16,'Channel Sub Channel'!$A$1:$A$918,0)-1,1,COUNTIF('Channel Sub Channel'!$A$1:$A$918,$N16),1))))

confirm with CTRL+SHIFT+ENTER

=IF($L14="Product",SUM(SUMIFS(Sales!$H:$H,Sales!$E:$E,OFFSET('Product Portfolio'!$A$1,MATCH($M14,'Product Portfolio'!$A$1:$A$918,0)-1,1,COUNTIF('Product Portfolio'!$A$1:$A$918,$M14),1),Sales!$D:$D,$H14,Sales!B:B,IF($K14=1,1,IF($K14=2,{1,2},IF($K14=3,{1,2,3},{1,2,3,4}))),Sales!$F:$F,TRANSPOSE(OFFSET('Channel Sub Channel'!$A$1,MATCH($N14,'Channel Sub Channel'!$A$1:$A$918,0)-1,1,COUNTIF('Channel Sub Channel'!$A$1:$A$918,$N14),1)))),IF($L14="Portfolio",SUMIFS(Sales!H:H,Sales!$B:$B,$K14,Sales!$D:$D,$H14),""))

Dear Barry,

When I added changed the criteria (highlighted in bold), the formula seems not to be working again. Could you kindly advise? Thanks!

Edit:
I figured a way out by changing the criteria as highlighted below. It works.. but processor intensive..

=IF($L19="Product",SUM(SUMIFS(Sales!$H:$H,Sales!$E:$E,OFFSET('Product Portfolio'!$A$1,MATCH($M19,'Product Portfolio'!$A$1:$A$918,0)-1,1,COUNTIF('Product Portfolio'!$A$1:$A$918,$M19),1),Sales!$D:$D,$H19,Sales!B:B,IF($K19=1,1,IF($K19=2,"<=2",IF($K19=3,"<=3","<=4"))),Sales!$F:$F,TRANSPOSE(OFFSET('Channel Sub Channel'!$A$1,MATCH($N19,'Channel Sub Channel'!$A$1:$A$918,0)-1,1,COUNTIF('Channel Sub Channel'!$A$1:$A$918,$N19),1)))),IF($L19="Portfolio",SUMIFS(Sales!H:H,Sales!$B:$B,$K19,Sales!$D:$D,$H19),""))

 

[barry houdini]

Simple example:

Your original formula was a bit like this:

=SUM(COUNTIFS(A:A,{"a";"b";"c"},B:B,{"x";"y";"z"}))

This will give a result but only counts 3 combinations, "a" in col A and "x" in col B, "b" in col A and "y" in col B or "c" in col A and "z" in col B. If we want to count all 9 combinations then we need to transpose one of the arrays, which can be done by changing the ; semi-colons in one of the arrays to , commas [note these separators may be different in non-English versions of excel but the same principle still applies]

=SUM(COUNTIFS(A:A,{"a";"b";"c"},B:B,{"x","y","z"}))

The COUNTIFS function returns a 3x3 matrix as a result, containing the 9 counts for each of the 9 combinations

Because it's not possible to have a "3d" matrix that means you are limited to two lists in this type of formula. For your latest version using

IF($K14=1,1,IF($K14=2,{1,2},IF($K14=3,{1,2,3},{1,2,3,4})))

.......you are effectively creating a 3rd list - that doesn't work, so your workaround fixes it [perhaps you could use just "<="&K$14?]

If you do need to have 3 or more multi-criteria lists then you need to switch to an array formula type setup, possibly using MATCH, e.g. for my example above

=SUMPRODUCT(ISNUMBER(MATCH(A2:A1000,{"a";"b";"c"},0))*ISNUMBER(MATCH(B2:B1000,{"x";"y";"z"},0)))

In that formula the separators in the arrays can be either semi-colon or comma, it doesn't matter (as long as you are consistent within each array) and you can add multiple lists