SumProduct Question

[Guzzlr]

=SUMPRODUCT(--(B13:B15="ABC"),C13:C15,--(E13:E15="ABC"),F13:F15,--(H13:H15="ABC"),I13:I15)

[TABLE="width: 512"]
<tbody>[TR]
[TD="class: xl65, width: 64"]ABC[/TD]
[TD="class: xl63, width: 64"]400.00[/TD]
[TD="class: xl64, width: 64"][/TD]
[TD="class: xl64, width: 64"]E[/TD]
[TD="class: xl64, width: 64"]300.0[/TD]
[TD="width: 64"][/TD]
[TD="class: xl64, width: 64"]E[/TD]
[TD="class: xl64, width: 64"]300.0[/TD]
[/TR]
[TR]
[TD="class: xl64"]A[/TD]
[TD="class: xl63"]450.00[/TD]
[TD="class: xl64"][/TD]
[TD="class: xl64"]A[/TD]
[TD="class: xl64"]600.0[/TD]
[TD][/TD]
[TD="class: xl64"]A[/TD]
[TD="class: xl64"]600.0[/TD]
[/TR]
[TR]
[TD="class: xl65"]ABC[/TD]
[TD="class: xl63"]0.89[/TD]
[TD="class: xl64"][/TD]
[TD="class: xl64"]ABC[/TD]
[TD="class: xl63"]0.50[/TD]
[TD][/TD]
[TD="class: xl64"]ABC[/TD]
[TD="class: xl63"]0.50[/TD]
[/TR]
</tbody>[/TABLE]

Hello Everyone

• I have the above formula and the above data.
• The data is in the range of: B13:I15 with two blank columns in between.
• The first ABC (top left) is in cell B13 and the 400 next to it is in cell C13.
• The last ABC (bottom right) is in cell H15 and the .5 next to it is in cell I15

My formula above is giving me an answer of .22 (.89*.5*.5)
What I'm trying to get, is the sum of all the ABC's (400+.89+.5+.5)=401.89
So I'm not having any luck with getting 401.89

Can someone help me please
Thanks for the help

 

[jtakw]

Hi,

The way your data is set up, you don't actually need SUMPRODUCT, a SUMIF formula will do the job, notice the off-set ranges.

L13 uses cell reference for your criteria, in case you might want to SUM something else ( A, E, etc. )
L14 has your criteria "ABC" hard-coded.

Book1
	B	C	D	E	F	G	H	I	J	K	L
12										Sum of	Result
13	ABC	400		E	300		E	300		ABC	401.89
14	A	450		A	600		A	600			401.89
15	ABC	0.89		ABC	0.5		ABC	0.5
Sheet578

Cell Formulas
Range	Formula
L13	=SUMIF(B13:H15,K13,C13:I15)
L14	=SUMIF(B13:H15,"ABC",C13:I15)

 

[aqeelnokia99]

=SUMPRODUCT(($A$1:$A$3=I1)*$B$1:$B$3+($D$1:$D$3=I1)*($E$1:$E$3)+($G$1:$G$3=I1)*($H$1:$H$3))

 

[Guzzlr]

Hi,

The way your data is set up, you don't actually need SUMPRODUCT, a SUMIF formula will do the job, notice the off-set ranges.

L13 uses cell reference for your criteria, in case you might want to SUM something else ( A, E, etc. )
L14 has your criteria "ABC" hard-coded.

	B	C	D	E	F	G	H	I	J	K	L
Sum of	Result
ABC	E	E	ABC
A	A	A
ABC	ABC	ABC

<tbody>
[TD="align: center"]12[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]13[/TD]

[TD="align: right"]400[/TD]
[TD="align: right"][/TD]

[TD="align: right"]300[/TD]
[TD="align: right"][/TD]

[TD="align: right"]300[/TD]
[TD="align: right"][/TD]

[TD="align: right"]401.89[/TD]

[TD="align: center"]14[/TD]

[TD="align: right"]450[/TD]
[TD="align: right"][/TD]

[TD="align: right"]600[/TD]
[TD="align: right"][/TD]

[TD="align: right"]600[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]401.89[/TD]

[TD="align: center"]15[/TD]

[TD="align: right"]0.89[/TD]
[TD="align: right"][/TD]

[TD="align: right"]0.5[/TD]
[TD="align: right"][/TD]

[TD="align: right"]0.5[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

Sheet578​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<tbody>[TR]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH]L13[/TH]
[TD="align: left"]=SUMIF(B13:H15,K13,C13:I15)[/TD]
[/TR]
[TR]
[TH]L14[/TH]
[TD="align: left"]=SUMIF(B13:H15,"ABC",C13:I15)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

Thank you all, this way above is working well for me.
Thank you

 

[jtakw]

You're welcome.