# Thread: SUMPRODUCT PROBLEM (XL2K SR1)

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

2. ## Re: SUMPRODUCT PROBLEM (XL2K SR1)

I think it's too complicated, I'd use the column of intermediate results.

3. ## Re: SUMPRODUCT PROBLEM (XL2K SR1)

Try the array (confirm with ctrl-shift-enter)

=SUM((A8:A13)*VLOOKUP(B8:B13,WCTbl,2,0)/100)

Steve

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

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

6. ## Re: SUMPRODUCT PROBLEM (XL2K SR1)

Thanks to all for the input. Looks like I'll be using an intermediate column.

Ken

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

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

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•