Print this page

Remove #Error! values from Custom Summary Formulas in Reports

Knowledge Article Number 000003362

The most common reason you'll find "#Error!" on a report cell is because of an error that happens when a formula has to divide a number by zero.

NOTE: Currency fields, although displayed as blank on the record and report, will always enter a 0.00 value in any formula.


Fix #Error! values

Add an IF statement that checks the value of the denominator.

For example, for the formula: SampleField__c:SUM / SampleField2__c:SUM
You write: IF(SampleField2__c:SUM = 0, SampleField__c:SUM, SampleField__c:SUM  / SampleField2__c:SUM)


"IF" statement considerations

  • Adding IF statements to your report might affect the report performance.
  • The Grand total of your report might differ from the sum of your values if your formula contains an IF statement.

For example, your custom summary formula is: IF( CLOSED:SUM = 0, 0, AMOUNT.CONVERT:SUM/CLOSED:SUM)
The report will appear like this:


User-added image


The grand total for your formula is different from the sum of the result of the formula for the records. This is due to the fact that the grand total is not the Sum of the numbers above, but is evaluating the formula at the summary level. As the Amount for the grand total is not equal to zero, the grand total will calculate at run time.


"AMOUNT.CONVERT:SUM/CLOSED:SUM" that in the example above is 500.000 / 4 = 125.000 and not (100.000+200.000 =) 300.000

Good to know: Smart Totaling can be another reason your grand total differs from the sum of your records. 


The same cause may return an "java.lang.ArithmeticException: Division undefined " error message when customizing a report.

promote demote