Results 1 to 8 of 8
  1. #1
    2 Star Lounger
    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
    Attached Files Attached Files

  2. #2
    Plutonium Lounger
    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.

  3. #3
    WS Lounge VIP sdckapr's Avatar
    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 ctrl-shift-enter)

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


    Steve

  4. #4
    5 Star Lounger
    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>

  5. #5
    WS Lounge VIP sdckapr's Avatar
    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

  6. #6
    2 Star Lounger
    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

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

  8. #8
    2 Star Lounger
    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

Posting Permissions

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