I have a template XLS file that I load with Apache POI and write loads of data in it, then save it as another file. I have formulas in my XLS file like this:

```
=SUMPRODUCT((DS!B:B="IN_THIS_ONLY")*(DS!D:D="New trade"))
```

also tried

```
=SUMPRODUCT(0+(DS!B:B="IN_THIS_ONLY"),0+(DS!D:D="New trade"))
```

these evaluate correctly if I press Enter on the cell in Excel. However, simply calling

```
HSSFFormulaEvaluator.evaluateAllFormulaCells(workbook);
```

does not seem to evaluate them, neither does pressing on the "Calculate now" button in Excel - so I guess this is a special formula or function.

The other, more conventional `COUNTIF`

s and `SUMIF`

s work fine, however these do not allow multiple conditions to be specified.

POI does not support array formulas.

Is there any way to make these work. I'm using POI version 3.7.

### Accepted Answer

One can press **CTRL-ALT-F9** to manually re-evaluate all formulas forcefully in Excel.

And here is the trick to make it work automatically on workbook open.

Add the following to your formula:

```
+(NOW()*0)
```

so for example, my `SUMPRODUCT`

above becomes

```
=SUMPRODUCT((DS!B:B="IN_THIS_ONLY")*(DS!D:D="New trade"))+(NOW()*0)
```

And this works! Excel now recalculates my special formula cells on open.

The reason for this is that `NOW()`

is a volatile function. Here is where I learned about this: http://msdn.microsoft.com/en-us/library/bb687891.aspx

`Application.CalculateFull`

also works, but only in Excel 2007 and later (and of course, one must enable macros to run). Unfortunately, in my case even though I use Excel 2007 my workbook will be opened by Excel 2003 users as well, so this was not an option.

The content is written by members of the stackoverflow.com community.

It is licensed under cc-wiki