Problems with SUM inside of SUMPRODUCT

[Mightystomp]

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

 

[Eric W]

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:

	B	C	D	E	F
Apples
Pears
Apples
Peaches
Grapes
Apples
Bananas
Apples
Apples

<colgroup><col style="width: 25pxpx"><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]4[/TD]
[TD="align: right"]81[/TD]
[TD="align: right"][/TD]

[TD="align: right"]1[/TD]
[TD="align: right"]2[/TD]

[TD="align: center"]5[/TD]
[TD="align: right"]81[/TD]
[TD="align: right"][/TD]

[TD="align: right"]2[/TD]
[TD="align: right"]4[/TD]

[TD="align: center"]6[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]3[/TD]
[TD="align: right"]6[/TD]

[TD="align: center"]7[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]4[/TD]
[TD="align: right"]8[/TD]

[TD="align: center"]8[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]5[/TD]
[TD="align: right"]10[/TD]

[TD="align: center"]9[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]6[/TD]
[TD="align: right"]12[/TD]

[TD="align: center"]10[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]7[/TD]
[TD="align: right"]14[/TD]

[TD="align: center"]11[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]8[/TD]
[TD="align: right"]16[/TD]

[TD="align: center"]12[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]9[/TD]
[TD="align: right"]18[/TD]

</tbody>

Sheet4​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]B4[/TH]
[TD="align: left"]=SUMPRODUCT((D4:D12="Apples")*E4:F12)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Array Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]B5[/TH]
[TD="align: left"]{=SUM(MMULT(TRANSPOSE((D4:D12="Apples")+0),E4:F12))}[/TD]
[/TR]
</tbody>[/TABLE]
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself[/TD]
[/TR]
</tbody>[/TABLE]



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]

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)