0

I used Poi 3.10 final for setting SUMIFS() formula in Excel(.xls)and I get the below exception:

newCell.setCellFormula(oldCell.getCellFormula());

'org.apache.poi.ss.formula.FormulaParseException: Name 'SUMIFS' is completely unknown in the current workbook'

Is there any way to bypass this and set the formula. Does Poi 3.10 support sumifs() or I have to use a different jar?

knix2
  • 327
  • 1
  • 5
  • 19
  • 1
    If you have just one criteria for summing, you can also use `SUMIF` instead of `SUMIFS` – kums Oct 31 '14 at 23:16
  • I manually entered =sumifs formula on this (97-3003)worksheet using excel2013. It works fine. But this throws error {=SUM((ups!A:A="United")*(ups!B:B="UL*")*(ups!C:C))} – knix2 Nov 04 '14 at 12:28
  • 1
    @pnuts Even if array formula to sum works, Here http://poi.apache.org/spreadsheet/formula.html it's given that the {=} type of formulas isn't supported – knix2 Nov 04 '14 at 12:37
  • =SUMPRODUCT((ups!A:A="United")*(ups!B:B="UL*"),ups!C:C) returns 0 instead of 20 – knix2 Nov 04 '14 at 16:48

1 Answers1

0

I have been looking for a solution for this issue and I haven't found a direct one, but there are some workarounds:

1- Using an alternative formula that is supported by Apache POI 3.1. In case of SUMIFS the alternative is SUMPRODUCT, it worked for me.

2- Setting the cell value as an HSSFRichTextString with the formula as its String (the "=" sign at the beginning of the formula should be included), and converting the field to a formula field using VB or any other method, this question might be of help in this case.

3- Same as the 2nd method but without converting the field type to formula. The user of the Workbook has to manually select the cell and press enter for the formula to be evaluated.

Community
  • 1
  • 1
Zaid Malhis
  • 588
  • 4
  • 18