41906
2007-03-20 09:41:27 +0000
bug in SUMIF formula
2008-05-06 08:56:51 +0000
1
1
1
Unclassified
POI
HSSF
3.0-dev
All
other
RESOLVED
FIXED
P2
normal
---
1
enterit
dev
6
oldest_to_newest
100679
0
enterit
2007-03-20 09:41:27 +0000
SUMIF(range;criteria;sum_range) formula with reference to another cell in
criteria part is not evaluated in Excel.
100680
1
enterit
2007-03-20 09:43:41 +0000
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.
100681
2
enterit
2007-03-20 09:45:09 +0000
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.
100947
3
enterit
2007-03-26 00:29:12 +0000
Hello.
Any news ?
116269
4
josh
2008-05-05 14:55:03 +0000
Re-tested in 3.1-beta1.
This was probably fixed in bug 44675 (The second argument (ReferencePtg) operand
class should be 'value' not 'reference')
116287
5
enterit
2008-05-06 00:51:12 +0000
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).
116304
6
josh
2008-05-06 08:56:51 +0000
(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'.