# Sumproduct help

#### mjbulldis

##### New Member
Here is a real brain teaser. Why is it not working?

=SUMPRODUCT((D13:D6055=B6080)*(B13:B6050,F13:F6050,H13:H6050*52,1/F13:F6050)/SUMPRODUCT(B13:B6050,F13:F6050))

This formula (w/o the condition) gives me the result I expect.
(B13:B6050,F13:F6050,H13:H6050*52,1/F13:F6050)/SUMPRODUCT(B13:B6050,F13:F6050)

Thanks in advance for the help

Mat

### Excel Facts

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.
On 2002-10-14 14:49, mjbulldis wrote:
Here is a real brain teaser. Why is it not working?

=SUMPRODUCT((D13:D6055=B6080)*(B13:B6050,F13:F6050,H13:H6050*52,1/F13:F6050)/SUMPRODUCT(B13:B6050,F13:F6050))

This formula (w/o the condition) gives me the result I expect.
(B13:B6050,F13:F6050,H13:H6050*52,1/F13:F6050)/SUMPRODUCT(B13:B6050,F13:F6050)

Thanks in advance for the help

Mat

Mat,

Would you explain in plain words what the first formula is expected to compute?

On 2002-10-14 14:55, Aladin Akyurek wrote:

=SUMPRODUCT((D13:D6055=B6080)*(B13:B6050,F13:F6050,H13:H6050*52,1/F13:F6050)/SUMPRODUCT(B13:B6050,F13:F6050))

Thanks in advance for the help

Mat

Mat,

Would you explain in plain words what the first formula is expected to compute?

[/quote]

=SUMPRODUCT((D13:D6055=B6080)*(B13:B6050,F13:F6050,H13:H6050*52,1/F13:F6050)/SUMPRODUCT(B13:B6050,F13:F6050))

Take all rows from 13 to 6055 and if the cell in collumn D is equal to B6080 (Buyer code) then sum the product of B13:B6050,F13:F6050,H13:H6050*52,1/F13:F6050 and devide it by SUMPRODUCT B13:B6050,F13:F6050

Mat

Can I multiply in a conditional sumproduct formula? For example, how do I get this to work:

Sumproduct((a1:a10="Some value")*(b1:b10,c1:c10))

Thanks,

Mat

On 2002-10-14 16:42, mjbulldis wrote:
Can I multiply in a conditional sumproduct formula? For example, how do I get this to work:

Sumproduct((a1:a10="Some value")*(b1:b10,c1:c10))

Thanks,

Mat

Let A1:C10 house the following sample...

{"a",3,5;"a",4,7;"b",5,6;"b",6,8;"a",5,6;"c",7,2;"a",8,7;"b",9,4;"a",5,5;"b",4,8}

and the condition that must hold for A1:A10 is "a", what is the expected result? 154?

Yes, the answer would be 154.

On 2002-10-14 17:15, mjbulldis wrote:

Yes, the answer would be 154.

The formula that follows the condition test...

=SUMPRODUCT((A1:A10="a")*(B1:B10),(A1:A10="a")*(C1:C10))

If you don't have any text or formula generated blanks (that is, ""), you can use the shorter...

=SUMPRODUCT((A1:A10="a")*(B1:B10)*(C1:C10))

You might also look at the array-entered SUM formulas like...

{=SUM(IF(A1:A10="a",B1:B10*C1:C10))}

Thanks Aladin. Here is the final formula. I ended up going the array route (seemed easier) It works great. I also included one additional condition.

Thanks again,

Mat

=SUM(IF((D\$13:D\$6050=B6517)*(H\$13:H\$6050>0),B\$13:B\$6050*F\$13:F\$6050*H\$13:H\$6050*52/F\$13:F\$6050))/SUM(IF((D\$13:D\$6050=B6517)*(H\$13:H\$6050>0),B\$13:B\$6050*F\$13:F\$6050))

On 2002-10-14 18:56, mjbulldis wrote:
Thanks Aladin. Here is the final formula. I ended up going the array route (seemed easier) It works great. I also included one additional condition.

Thanks again,

Mat

=SUM(IF((D\$13:D\$6050=B6517)*(H\$13:H\$6050>0),B\$13:B\$6050*F\$13:F\$6050*H\$13:H\$6050*52/F\$13:F\$6050))/SUM(IF((D\$13:D\$6050=B6517)*(H\$13:H\$6050>0),B\$13:B\$6050*F\$13:F\$6050))

That's great.

Try the following to avoid double computations...

=SUM(IF(SETV((D\$13:D\$6050=B6517)*(H\$13:H\$6050>0),1),(SETV(B\$13:B\$6050*F\$13:F\$6050),2)*H\$13:H\$6050*52/F\$13:F\$6050))/SUM(IF(GETV(1),GETV(2)))

still array-entered.

http://longre.free.fr/english/index.html

Replies
0
Views
1K
Replies
0
Views
497
Replies
15
Views
2K
Replies
1
Views
146
Replies
3
Views
737

1,221,525
Messages
6,160,326
Members
451,637
Latest member
hvp2262

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

### Which adblocker are you using?

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

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