Problems with SUM inside of SUMPRODUCT

Mightystomp

New Member
Joined
Jan 31, 2006
Messages
48
I am having problems using a SUM formula as par of a SUMPRODUCT formula. The formula is as follows.


=SUMPRODUCT(--(D4:D12="Apples");SUM(E4:F12))

What i am trying to achieve is that it multiplies the 0 or 1 resulting from the D4 check with the sum of E4:F4, then the 0 or 1 from the D5 check with the sum of E5:F5 etc.

As it is it calculates the sum(E4:F12) as one value and not an array.


Same problem arises when trying a vlookup in the same way

=SUMPRODUCT(E4:E12;VLOOKUP(D4:D12;Priser!$F$3:$R$5;2;0))

I am trying to get it to multiply the E4 value with the value found of vlookup(D4;Priser!$F$3:$R$5;2;0)m then add the product of E5 multiplied with the result of vlookup(D5;Priser!$F$3:$R$5;2;0) etc.


What I am really trying to to is calculating YTD Sales in a data set with critera the first columns then units sold in Jan, Feb etc in the following columns. Prices are in another data set.

I know I can do it quite easily if using multiple cells to do the calculation. Im just curious if it can be all done in one cell.


Thank you
 

Some videos you may like

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)

Eric W

MrExcel MVP
Joined
Aug 18, 2015
Messages
10,626
As you've seen, using SUM inside SUMPRODUCT doesn't work, since it evaluates the entire SUM before the rest of the processing occurs. You need to find a way to keep the values distinct until you're ready for them. Two ways:

BCDEF
481Apples12
581Pears24
6Apples36
7Peaches48
8Grapes510
9Apples612
10Bananas714
11Apples816
12Apples918

<colgroup><col style="width: 25pxpx"><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>
Sheet4

Worksheet Formulas
CellFormula
B4=SUMPRODUCT((D4:D12="Apples")*E4:F12)

<thead>
</thead><tbody>
</tbody>

<tbody>
</tbody>

Array Formulas
CellFormula
B5{=SUM(MMULT(TRANSPOSE((D4:D12="Apples")+0),E4:F12))}

<thead>
</thead><tbody>
</tbody>
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>



As far as your VLOOKUP question, the same kind of thing is true, the various lookup functions don't work well in array formulas. It can still be done, but it would need some kind of MMULT function. I'll figure that out if you want, but it'd be more complicated than the MMULT formula here, and would you want to maintain that?
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,201
1. Control+shift+enter, not just enter, which would withstand any blanks or other text in the sum range...

=SUM(IF(D4:D12="apples",E4:F12))

2. Judging from the non-working formula with VLOOKUP, it looks like...

=SUMPRODUCT(SUMIFS(Priser!$G$3:$G$5,Priser!$F$3:$F$5,$D$4:$D$12),$E$4:$E$12)
 

Watch MrExcel Video

Forum statistics

Threads
1,122,822
Messages
5,598,307
Members
414,224
Latest member
Crazy_FC

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
Top