Grand Total in Salesforce Reports Doesn't Match Custom Summary Formula Totals

Why the Grand Total Differs

The Grand Total for a Custom Summary Formula is calculated independently at runtime against all records included in the report. It is not the sum of the formula values displayed in each grouping. This behavior is expected and is by design.

Another reason for a mismatch is Smart Totaling, particularly when currency conversion is used. For more information, see Report total doesn't match the total in Excel or in a Dashboard table component.

Example: Custom Summary Formula with Negative Values

Example Custom Summary Formula added to an Opportunity report:

IF(AMOUNT.CONVERT:SUM < 0, 0, AMOUNT.CONVERT:SUM)

Report setup

• Format: Summary Report

• Grouping 1: Account Name

• Grouping 2: Opportunity Owner

Report data

• One Opportunity for UserA with a converted amount of -365.25

• Two Opportunities for UserB with a total converted amount of 401

What the report shows

• For UserA, the converted amount is negative, so the formula evaluates to 0.

• For UserB, the converted amount is positive, so the formula displays 401.

• For the Grand Total, Salesforce recalculates the formula using the combined Opportunity amounts (300 + 101 - 365 = 36). Since the overall total is greater than zero, the formula returns 36, rather than the sum of the grouped formula values (0 + 401 = 401).

Image alt text

"Screenshot showing a summary report with two groupings—Account Name and Opportunity Owner—where the Grand Total row displays a different value than the sum of the Custom Summary Formula column for the individual groupings."