PDA

View Full Version : Solved: Sumproduct result displays incorrectly on sheet



Shred Dude
03-11-2011, 12:03 AM
I have a simple sumproduct formula as follows:

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

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?

Shred Dude
03-11-2011, 12:12 AM
This formula :

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

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???

Bob Phillips
03-11-2011, 03:03 AM
TRy array-entering the SP formula.

Bob Phillips
03-11-2011, 03:04 AM
Or remove the IFERROR(...,0)

Shred Dude
03-11-2011, 10:12 AM
Array Entering did the trick. Now I can expand the formula to capture all the constraints I need. Thx.