SumProduct Question

Guzzlr

Well-known Member
Joined
Apr 20, 2009
Messages
925
Office Version
  1. 2016
Platform
  1. Windows
Code:
=SUMPRODUCT(--(B13:B15="ABC"),C13:C15,--(E13:E15="ABC"),F13:F15,--(H13:H15="ABC"),I13:I15)

ABC400.00E300.0E300.0
A450.00A600.0A600.0
ABC0.89ABC0.50ABC0.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
 
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:
Upvote 0
=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))

8855793151d504b1a4d0ec31425bd8f2fcd45263abd4f0894f05cc3ee0f1d02d6b277c4c.jpg
 
Upvote 0
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:
Upvote 0

Forum statistics

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

Which adblocker are you using?

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