SumProduct Question

[Guzzlr]

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

ABC	400.00		E	300.0		E	300.0
A	450.00		A	600.0		A	600.0
ABC	0.89		ABC	0.50		ABC	0.50

<tbody>
</tbody>

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
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

<tbody>
</tbody>

Sheet578​

Worksheet Formulas Cell Formula L13 =SUMIF(B13:H15,K13,C13:I15) L14 =SUMIF(B13:H15,"ABC",C13:I15) <tbody> </tbody>

<tbody>
</tbody>

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

 

[jtakw]

You're welcome.