Dynamic SumProduct to either use key or all values.

[Scotster]

Firstly, apolgoies I can't upload any files due to security limitations. I've tried to give the simplest example while covering all the info I hope required.

I'm trying to use Sum product to create charting information from quite a large dataset. I can get it working exactly as I want to, but the formula seems unwieldy. I can't imagine it needs to be this complex so just wondering if anyone can help make it a bit more succinct.

I've created a small example that I hope makes some sense. I've highlighted the formula cells in bold. What I'm looking for is a sum product of the quantities where certain criteria are met.

449 Highlighted cell :

Filter option $A20 is the issue

=SUMPRODUCT(($D$2:$I$16)*($D$1:$I$1=$A20)*($B$2:$B$16=D$19)*($C$2:$C$16=$B$18))

Above works perfectly if there I want a specific value. It doesn't work in D30 where the filter value is "<>". I'm guessing because ="<>" isn't allowed and should instead be <>""

I've gotten around this with the following:

=IF($B$28="<>",SUMPRODUCT(($D$2:$I$16)*($D$1:$I$1=$A30)*($B$2:$B$16=D$29)),SUMPRODUCT(($D$2:$I$16)*($D$1:$I$1=$A30)*($B$2:$B$16=D$29)*($C$2:$C$16=$B$28)))

This works but in the actual formula I'm using a potential 3 dynamic filter options for the charts so it looks like the following:

=IF(AND(C2="<>",C3="<>"),SUMPRODUCT((Summary!$N$9:$CJ$1498)*(Summary!$G$9:$G$1498=C$4)*(Summary!$N$7:$CJ$7=1)*(Summary!$L$9:$L$1498=$B6)),
IF(AND(C2="<>",C3<>"<>"),SUMPRODUCT((Summary!$N$9:$CJ$1498)*(Summary!$G$9:$G$1498=C$4)*(Summary!$N$7:$CJ$7=1)*(Summary!$K$9:$K$1498=C$3)*(Summary!$L$9:$L$1498=$B6)),
IF(AND(C2<>"<>",C3="<>"),SUMPRODUCT((Summary!$N$9:$CJ$1498)*(Summary!$G$9:$G$1498=C$4)*(Summary!$N$7:$CJ$7=1)*(Summary!$H$9:$H$1498=C$2)*(Summary!$L$9:$L$1498=$B6)),
SUMPRODUCT((Summary!$N$9:$CJ$1498)*(Summary!$G$9:$G$1498=C$4)*(Summary!$N$7:$CJ$7=1)*(Summary!$H$9:$H$1498=C$2)*(Summary!$K$9:$K$1498=C$3)*(Summary!$L$9:$L$1498=$B6)))))

This doesn't seem right so I'm just wondering if there is a more succinct way of creating the product total?

			1​	1​	1​	2​	2​	2​
Part1	BU1	P1	28​	19​	147​	179​	90​	93​
Part2	BU2	P1	94​	10​	10​	113​	153​	105​
Part3	BU3	P1	53​	114​	92​	137​	175​	87​
Part4	BU1	P1	172​	74​	163​	66​	131​	57​
Part5	BU2	P1	159​	145​	67​	172​	43​	151​
Part6	BU3	P1	47​	170​	76​	195​	125​	37​
Part7	BU1	P1	195​	107​	154​	69​	156​	127​
Part8	BU2	P1	108​	90​	48​	179​	12​	88​
Part9	BU3	P2	180​	148​	68​	64​	23​	122​
Part10	BU1	P2	80​	97​	4​	193​	50​	127​
Part11	BU2	P2	34​	7​	4​	68​	199​	89​
Part12	BU3	P2	85​	171​	114​	94​	118​	33​
Part13	BU1	P2	16​	54​	198​	3​	74​	18​
Part14	BU2	P2	198​	155​	85​	20​	22​	60​
Part15	BU3	P2	22​	171​	19​	182​	90​	182​
Filter	P2
			BU1	BU2	BU3
1​			449​	483​	978​
2​			465​	458​	908​
Filter	P1
			BU1	BU2	BU3
1​			1059​	731​	552​
2​			968​	1016​	756​
Filter	<>
			BU1	BU2	BU3
1​			0​	0​	0​
2​			0​	0​	0​

 

[RobP]

This is one possibility that might help you - Providing your data is as you show it (with columns D:F having your "1" data, G:I having your "2" data)

This formula takes advantage of knowing where the "1" Cols are, and "2" cols are etc..

If you can't layout the data, then it won't work for you, sorry. You can amend it for other datas as you have, but essentially I used it to Filter P1 data to get same results as you in second block.

Book1
	B	C	D	E	F
18	P1		BU1	BU2	BU3
19	1		1059	731	552
20	2		968	1016	756
Sheet1

Cell Formulas
Range	Formula
D19	D19	=BYCOL(TOCOL(FILTER($D1:$F16,($C1:$C16="P1")*($B1:$B16="BU1"),"empty")),LAMBDA(col,SUM(col)))
E19	E19	=BYCOL(TOCOL(FILTER($D1:$F16,($C1:$C16="P1")*($B1:$B16="BU2"),"empty")),LAMBDA(col,SUM(col)))
F19	F19	=BYCOL(TOCOL(FILTER($D1:$F16,($C1:$C16="P1")*($B1:$B16="BU3"),"empty")),LAMBDA(col,SUM(col)))
D20	D20	=BYCOL(TOCOL(FILTER($G1:$I16,($C1:$C16="P1")*($B1:$B16="BU1"),"empty")),LAMBDA(col,SUM(col)))
E20	E20	=BYCOL(TOCOL(FILTER($G1:$I16,($C1:$C16="P1")*($B1:$B16="BU2"),"empty")),LAMBDA(col,SUM(col)))
F20	F20	=BYCOL(TOCOL(FILTER($G1:$I16,($C1:$C16="P1")*($B1:$B16="BU3"),"empty")),LAMBDA(col,SUM(col)))