Sumproduct help

[mjbulldis]

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

 

[Aladin Akyurek]

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]

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]

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]

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?

 

[mjbulldis]

Aladin,

Yes, the answer would be 154.

 

[Aladin Akyurek]

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]

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]

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