SUMIF(range;criteria;sum_range) formula with reference to another cell in criteria part is not evaluated in Excel.
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.
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.
Hello. Any news ?
Re-tested in 3.1-beta1. This was probably fixed in bug 44675 (The second argument (ReferencePtg) operand class should be 'value' not 'reference')
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).
(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'.