Consulting

Results 1 to 5 of 5

Thread: Solved: Sumproduct result displays incorrectly on sheet

  1. #1

    Solved: Sumproduct result displays incorrectly on sheet

    I have a simple sumproduct formula as follows:

    [VBA]=SUMPRODUCT(--(INDEX(Contracts,,2)=M4)*IFERROR((INDEX(Contracts,,5)/28),0))[/VBA]

    I'm attempting to match the value in cell M4 in the 2nd column of the Range "Contracts" and then sum up the value in column 5.

    If i view the result in the Fx Formula Arguments window by clicking the Fx on the formula bar, I see the Formula Result I would expect given my data. On my sheet however, there is a different number being dislayed.

    I can change the value in M4 to other valid values and the same occurs. The correct result shows in the Formula Result, but not on the sheet.

    Anyone ever experienced this?

  2. #2
    This formula :

    [VBA]=SUMIF(INDEX(Contracts,,2),"=" & M4,INDEX(Contracts,,5))/28[/VBA]

    generates the exact same result displayed as the Formula result in the Fx window for the sumproduct formula above, but it also displays the correct result on the worksheet.

    Why would the sumproduct formula show one result on the worksheet and another in the Function Arguments window???

  3. #3
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,418
    Location
    TRy array-entering the SP formula.
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

  4. #4
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,418
    Location
    Or remove the IFERROR(...,0)
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

  5. #5
    Array Entering did the trick. Now I can expand the formula to capture all the constraints I need. Thx.

Posting Permissions

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