Sumproduct help

mjbulldis

New Member
Joined
Oct 13, 2002
Messages
5
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

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,209
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?
 

mjbulldis

New Member
Joined
Oct 13, 2002
Messages
5
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
 

mjbulldis

New Member
Joined
Oct 13, 2002
Messages
5
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
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,209

ADVERTISEMENT

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?
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,209

ADVERTISEMENT

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

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))}
 

mjbulldis

New Member
Joined
Oct 13, 2002
Messages
5
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))
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,209
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.

The SETV/GETV pair is part of the morefunc.xll add-in, downloadable from:

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

Forum statistics

Threads
1,144,360
Messages
5,723,903
Members
422,526
Latest member
edminty84

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