Results 1 to 8 of 8
Thread: SUMPRODUCT PROBLEM (XL2K SR1)

20070522, 21:44 #1
 Join Date
 Dec 2000
 Location
 Sault Ste. Marie, Michigan, USA
 Posts
 102
 Thanks
 0
 Thanked 0 Times in 0 Posts
SUMPRODUCT PROBLEM (XL2K SR1)
I'm having a problem with a sumproduct formula where I want to cross multiply two arrays. The first array is a range of six cells. The second is a six element array created with the offset command.
When I select the individual arrays in the formula bar and press F9, I can see all the correct elements displayed, but my result of the sumproduct is zero.
Can someone help out. This seems like it should work.
Thanks,
Ken

20070522, 22:12 #2
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 29 Times in 29 Posts
Re: SUMPRODUCT PROBLEM (XL2K SR1)
I think it's too complicated, I'd use the column of intermediate results.

20070523, 01:25 #3
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,225
 Thanks
 14
 Thanked 342 Times in 335 Posts
Re: SUMPRODUCT PROBLEM (XL2K SR1)
Try the array (confirm with ctrlshiftenter)
=SUM((A8:A13)*VLOOKUP(B8:B13,WCTbl,2,0)/100)
Steve

20070523, 15:23 #4
 Join Date
 Aug 2004
 Location
 Connecticut, USA
 Posts
 816
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: SUMPRODUCT PROBLEM (XL2K SR1)
Steve,
The formula is taking the result of the first VLOOKUP and using that to calculate against the rest of the rows.
<table border 1><td>Salary</td><td>WC Code</td><td>VLOOKUP Value</td><td>Vlookup*Sal/100</td><td>$0.39*Salary/100</td><td>18014.88</td><td>8810</td><td>0.39</td><td>70.26</td><td>70.26</td><td>46161.44</td><td>8832</td><td>0.54</td><td>249.27</td><td>180.03</td><td>6995.46</td><td>9015</td><td>5.32</td><td>372.16</td><td>27.28</td><td>14428.54</td><td>8835</td><td>4.3</td><td>620.43</td><td>56.27</td><td>30885.92</td><td>8835</td><td>4.3</td><td>1328.09</td><td>120.46</td><td>11962.91</td><td>8810</td><td>0.39</td><td>46.66</td><td>46.66</td><td> </td><td> </td><td>Totals:</td><td>2686.87</td><td>500.95</td></table>

20070523, 16:50 #5
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,225
 Thanks
 14
 Thanked 342 Times in 335 Posts
Re: SUMPRODUCT PROBLEM (XL2K SR1)
This seems to be a "bug" in calculating the array in XL that I missed when I created it (and seems to be in both XL97 and XL2002 in my subsequent tests and XL2000 based on your comments)
I created the entire 6 row array and built it in steps. When I got to the end I had 6 rows with the "2686" value which means that the array in a single cell should give this value. I did not do confirm this (which is why I missed it).
If you put the array calculation in more than 1 cell, it calculates correctly, but if only in 1 cell, XL seems confused by it and miscalculates. It seems you will have create a 2 row array and hide the 2nd value or use the intermediate calculation route, unless someone else has a workaround to the bug
Steve

20070523, 21:29 #6
 Join Date
 Dec 2000
 Location
 Sault Ste. Marie, Michigan, USA
 Posts
 102
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: SUMPRODUCT PROBLEM (XL2K SR1)
Thanks to all for the input. Looks like I'll be using an intermediate column.
Ken

20070528, 19:02 #7
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 29 Times in 29 Posts
Re: SUMPRODUCT PROBLEM (XL2K SR1)
Try this one:
=SUMPRODUCT(A8:A13,SUMIF(F7:F30,B8:B13,G7:H30))/100
It's a normal formula, not an array formula (the array is already implicit in SUMPRODUCT).

20070529, 02:12 #8
 Join Date
 Dec 2000
 Location
 Sault Ste. Marie, Michigan, USA
 Posts
 102
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: SUMPRODUCT PROBLEM (XL2K SR1)
Great solution, Hans. I didn't even think to use SUMIF for this. Funny thing is that I was playing around with the problem today, and came across a solution using LOOKUP instead of VLOOKUP or MATCH/INDEX. Like this:
=SUMPRODUCT(A8:A13*LOOKUP(B8:B13,F7:F30,G7:G30))/100
However, I think your solution is preferable because LOOKUP is fussy about the search order and thus is more prone to error if something is not in the proper order.
Thanks,
Ken