Bug 45206 - sumproduct formula returning #VALUE
Summary: sumproduct formula returning #VALUE
Status: RESOLVED DUPLICATE of bug 45060
Alias: None
Product: POI
Classification: Unclassified
Component: HSSF (show other bugs)
Version: unspecified
Hardware: PC Windows XP
: P2 normal (vote)
Target Milestone: ---
Assignee: POI Developers List
URL:
Keywords:
Depends on:
Blocks:
 
Reported: 2008-06-14 17:12 UTC by geo
Modified: 2008-06-15 15:28 UTC (History)
0 users


Attachments
Add an attachment (proposed patch, testcase, etc.)

Note You need to log in before you can comment on or make changes to this bug.
Description geo 2008-06-14 17:12:40 UTC 
The following example is not an array product. When you create an excel file and use the following forumula, we see a #VALUE. However if you click on the cell and press enter, the number gets populated. I am guessing this is some kind of bug related to the unique way this formula works. 

=SUMPRODUCT(('SHEET1'!C3:C751=Summary!F22)*('SHEET1'!D3:D751=Summary!G22),'SHEET1'!AF3:AF751)
Comment 1 Josh Micich 2008-06-15 15:28:36 UTC 
Looks like this was fixed with bug 45060. This bug is visible in 3.1-beta2 but the fix should be available in 3.1-final.  It is also available in svn trunk.

Just to be sure, the existing unit test was modified (svn r668014) to add this example. Here is a trace from successful execution of the new test case:

SUMPRODUCT((Sheet1!C3:C751=Sheet1!F22)*(Sheet1!D3:D751=Sheet1!G22),Sheet1!AF3:AF751)
  token[0] org.apache.poi.hssf.record.formula.Area3DPtg [sheetIx=0 ! C3:C751] A
  token[1] org.apache.poi.hssf.record.formula.Ref3DPtg [sheetIx=0 ! F22] A
  token[4] org.apache.poi.hssf.record.formula.Area3DPtg [sheetIx=0 ! D3:D751] A
  token[5] org.apache.poi.hssf.record.formula.Ref3DPtg [sheetIx=0 ! G22] A
  token[9] org.apache.poi.hssf.record.formula.Area3DPtg [sheetIx=0 ! AF3:AF751] A
  token[10] org.apache.poi.hssf.record.formula.FuncVarPtg [SUMPRODUCT nArgs=2] V

In 3.1-beta2, tokens 0,1,4,5 are erroneously encoded as operand class 'V'





*** This bug has been marked as a duplicate of bug 45060 ***