A pivot table can count entries when you expected it to sum amounts. Inspect the measure’s aggregation and the source types before changing the displayed total.
Check Summarize by
Open the pivot editor and inspect the field under Values. SUM, COUNTA, COUNT, and AVERAGE represent different questions. A count of nonempty amount entries is not the amount itself.
Check whether the same measure was added more than once or whether a calculated field applies another transformation. Record the existing configuration before making changes.
Inspect the contributing records
Use supporting-row details for an unexpected pivot cell and compare those records with the source. Test numeric-looking amounts using ISNUMBER. Formatting a text value as currency does not guarantee numeric storage.
Look for duplicate transactions, mixed currencies, source subtotal rows, and inconsistent category labels. A correct SUM will still double-count duplicated inputs or combine amounts that should not share one unit.
Reconcile a small group
Manually total one group and compare both its record count and amount. Then check the full source range and active pivot filters. A filter can explain a lower grand total even when the aggregation is correct.
Repair the source or configuration and verify the result before adding cosmetic formatting. Do not type a replacement value into the pivot output to make a report match an expectation. If a summary should exclude a record, establish the appropriate source or filter rule and retain the reason. Keep an exception list for malformed amounts rather than silently replacing them with zero and presenting the result as a complete financial total.
References
Reviewed against the linked official product guidance on November 3, 2026. Features, limits, screen labels, and access can vary by account, platform, region, and organization policy.