Placing a cell range in a formula results in a #VALUE! in the excel sheet if the range rows differ. For example, "SUM(G22:G22)" evaluates properly by summing the contents of cell G22 "SUM(G22:G28)" results in a #VALUE! in the excel document. This functionality worked fine as of poi-bin-3.0.1-FINAL-20070705, but does not work in poi-3.1-beta1. A sample code snippet that sets the formula contents can be found below: HSSFCellStyle summaryDataStyle = wb.createCellStyle(); summaryDataStyle.setAlignment(HSSFCellStyle.ALIGN_RIGHT); summaryDataStyle.setFont(summaryItemFont); summaryDataStyle.setDataFormat((short) 3); ... HSSFCell currentCell = row.createCell((short)columnIndex++); currentCell.setCellStyle(summaryDataStyle); currentCell.setCellType(HSSFCell.CELL_TYPE_FORMULA); currentCell.setCellFormula("SUM(G"+(previousItemStartRow+1)+":G"+row.getRowNum()+")"); where previousItemStartRow is an int. Very strange that it evaluates correctly for a range that resolves to a single cell, but not for a real range of cells. Again, the EXACT same code works fine under the above 3.0 release.
Note that SUM(G7,G8) Works fine as well
Reproduced in 3.1-beta1 This problem was resolved in the follow-up fix for bug 44675 (svn r652994). You can either use the latest svn trunk or wait for final 3.1 to get this fix.