Bug 41906

Summary: bug in SUMIF formula
Product: POI Reporter: enterit <enterit>
Component: HSSFAssignee: POI Developers List <dev>
Status: RESOLVED FIXED    
Severity: normal    
Priority: P2    
Version: 3.0-dev   
Target Milestone: ---   
Hardware: All   
OS: other   

Description enterit 2007-03-20 09:41:27 UTC
SUMIF(range;criteria;sum_range) formula with reference to another cell in 
criteria part is not evaluated in Excel.
Comment 1 enterit 2007-03-20 09:43:41 UTC
For example, 
SUMIF(A1:A100;B1;E1:E100) 
- Excel does not recalculate. It leaves zero in such cell.

but
SUMIF(A1:A100;B1+0;E1:E100) 
- Excel recalculates properly.

I used Excel 2007.
Comment 2 enterit 2007-03-20 09:45:09 UTC
Test code:

        HSSFWorkbook book = new HSSFWorkbook();
        HSSFSheet sheet = book.createSheet();

        HSSFRow row0 = sheet.createRow(0);
        row0.createCell((short)0).setCellValue(1);
        row0.createCell((short)1).setCellValue(1);
        row0.createCell((short)2).setCellValue(3);
        row0.createCell((short)3).setCellFormula("SUMIF(A1:A100;B1;C1:C100)");

        HSSFRow row1 = sheet.createRow(1);
        row1.createCell((short)0).setCellValue(1);
        row1.createCell((short)1).setCellValue(1);
        row1.createCell((short)2).setCellValue(3);
        row1.createCell((short)3).setCellFormula("SUMIF(A1:A100;B1+0;C1:C100)");
        
        FileOutputStream fos = new FileOutputStream("book.xls");
        book.write(fos);
        fos.close();

First row in resulting workbook contains formula, that is not calculated by 
Excel.
Second row contains formula, that is calculated properly.
Comment 3 enterit 2007-03-26 00:29:12 UTC
Hello.
Any news ?
Comment 4 Josh Micich 2008-05-05 14:55:03 UTC
Re-tested in 3.1-beta1.

This was probably fixed in bug 44675 (The second argument (ReferencePtg) operand
class should be 'value' not 'reference')
Comment 5 enterit 2008-05-06 00:51:12 UTC
Why the second argument should be 'value' not 'reference'?
I seems Excel supports reference as the second argument.
I am able to create cell with formula "=SUMIF(A1:A100;B1;C1:C100)" using Excel 2007.
I think that current POI behaviour (3.1-beta) has regression, since it does not allow to create such formulas (neither "..;B1;.." nor "..;B1+0;.." can be created).
Comment 6 Josh Micich 2008-05-06 08:56:51 UTC
(In reply to comment #5)
> I think that current POI behaviour (3.1-beta) has regression, 
Sorry I mis-typed. I meant "Re-tested in 3.1-beta2". Bug 44675 was completed after 3.1-beta1.  You can either wait for the beta2 release, grab a nightly build, or build directly from svn trunk.


> Why the second argument should be 'value' not 'reference'?
> I seems Excel supports reference as the second argument.
'Operand classes' are different to token types.  If the second arg is a reference, the token type will be 'tRef'.  Depending on the operand class, it will be 'tRefR', 'tRefV', or 'tRefA'.