# SumProduct Question

#### Guzzlr

##### Well-known Member
Code:
``=SUMPRODUCT(--(B13:B15="ABC"),C13:C15,--(E13:E15="ABC"),F13:F15,--(H13:H15="ABC"),I13:I15)``

 ABC 400 E 300 E 300 A 450 A 600 A 600 ABC 0.89 ABC 0.5 ABC 0.5

<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

Last edited:

### Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!
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
BCDEFGHIJKL
12Sum ofResult
13ABC400E300E300ABC401.89
14A450A600A600401.89
15ABC0.89ABC0.5ABC0.5
Sheet578
Cell Formulas
RangeFormula
L13=SUMIF(B13:H15,K13,C13:I15)
L14=SUMIF(B13:H15,"ABC",C13:I15)

Last edited:
=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))

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.

BCDEFGHIJKL
12Sum ofResult
13ABC400E300E300ABC401.89
14A450A600A600401.89
15ABC0.89ABC0.5ABC0.5

<tbody>
</tbody>
Sheet578

Worksheet Formulas
CellFormula
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

Last edited:
You're welcome.

Replies
0
Views
457
Replies
1
Views
151
Replies
3
Views
316
Replies
15
Views
791
Replies
6
Views
502

Threads
1,203,356
Messages
6,054,927
Members
444,759
Latest member
TeckTeck

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

### Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

### Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back