How do I find why two reports have different totals?

Break both down by the same dimension, join the breakdowns, and keep rows with different values or rows missing from one side.

Why use a full outer join?

It keeps rows that exist in only one report, which a normal join or a one-sided lookup would hide.

What causes most report mismatches?

Different definitions (dates, gross vs net), filters and mappings, more often than wrong data.

How do I ignore tiny rounding differences?

Filter on the absolute difference above a tolerance, such as 1, instead of any difference.

What is a reconciliation bridge?

A short table that starts from one report's total, lists each explained difference, and ends at the other report's total.

Can I compare on more than one column?

Yes. Join on a combined key, such as region and month, to locate differences in one pass.

Why don't these two reports add up to the same total?

By the Analistable team · Updated 2026-10-07 · 3 min read

Break both totals down by the same column (region, product, month), join the two breakdowns, and keep rows where the values differ or a row exists in only one report. A full outer join does both. In the example, finance shows 1,200 more for South and includes an East region the CRM report doesn't have at all.

Part of our guide: How to answer questions across multiple spreadsheets

The data

Revenue by region from two systems
Region	Finance report	CRM report
North	48200	48200
South	61950	60750
West	39400	39400
East	22100	—

In a spreadsheet

Regions    =UNIQUE(VSTACK(Finance[Region], CRM[Region]))
Finance    =SUMIFS(Finance[Revenue], Finance[Region], A2)
CRM        =SUMIFS(CRM[Revenue], CRM[Region], A2)
Difference =[@Finance] - [@CRM]

Building the list of regions from both reports is what catches rows that exist on only one side.

In SQL

SELECT COALESCE(f.region, c.region) AS region,
       f.revenue AS finance, c.revenue AS crm,
       COALESCE(f.revenue, 0) - COALESCE(c.revenue, 0) AS difference
FROM finance f
FULL OUTER JOIN crm c ON c.region = f.region
WHERE f.revenue IS DISTINCT FROM c.revenue;

Result
region	finance	crm	difference
South	61950	60750	1200
East	22100	NULL	22100

Then drill down

Repeat the comparison one level lower for the regions that differ — by customer or invoice within South — until you find the rows behind the gap.
Check definitions: booked vs invoiced, gross vs net, invoice date vs close date. Many mismatches are definitional, not errors.
Check filters: a region missing from one report (East) often means a filter or a mapping, not missing sales.

Ask it in Analistable: “Compare revenue by region between the finance and CRM exports and show where they differ.” Background: comparing two spreadsheets.

Drill-down example

South, by customer
Customer	Finance	CRM	Difference
Bakery Lune	24300	24300	0
Hart & Co	18450	17250	1200
Nordic Supply	19200	19200	0

The 1,200 sits in one customer. Repeating the join on invoices for Hart & Co shows a credit note recorded in the CRM but not yet in finance — a timing difference, not a data error.

Build a bridge from one total to the other

Finance total to CRM total
Step	Amount
Finance report total	171650
Less: East region, not in CRM report	-22100
Less: Hart & Co credit note, not yet in finance	-1200
CRM report total	148350

Finance: 48,200 + 61,950 + 39,400 + 22,100 = 171,650. CRM: 48,200 + 60,750 + 39,400 = 148,350. The gap of 23,300 is fully explained by two items. A bridge like this is what reviewers want to see: every difference named, nothing left as “other”.

Ignore rounding noise

WHERE ABS(COALESCE(f.revenue, 0) - COALESCE(c.revenue, 0)) > 1

Reports calculated in different systems often differ by cents after currency conversion or tax rounding. Replace the IS DISTINCT FROM condition with a tolerance so only real differences remain. In Excel, filter the Difference column with =ABS([@Difference]) > 1.

Excel 2019 and Google Sheets

Excel 2019 / 2016: paste both reports' regions into one column, use Remove Duplicates, then SUMIFS from each report — the same logic as the UNIQUE(VSTACK()) version.
Google Sheets: =UNIQUE({Finance!A2:A; CRM!A2:A}) builds the region list; SUMIF does the rest.
Many dimensions: compare on a combined key (region & month & product) so a difference can be located in one pass.

Troubleshooting

Common causes
Symptom	Cause	Fix
Every row differs by a similar %	One report includes VAT or uses another currency	Compare on the same basis
Differences only at month ends	Different date used (invoice vs payment)	Align the date field
Region names don't join	“South” vs “South ” or “S”	Trim and map names first

Frequently asked questions

How do I find why two reports have different totals?: Break both down by the same dimension, join the breakdowns, and keep rows with different values or rows missing from one side.
Why use a full outer join?: It keeps rows that exist in only one report, which a normal join or a one-sided lookup would hide.
What causes most report mismatches?: Different definitions (dates, gross vs net), filters and mappings, more often than wrong data.
How do I ignore tiny rounding differences?: Filter on the absolute difference above a tolerance, such as 1, instead of any difference.
What is a reconciliation bridge?: A short table that starts from one report's total, lists each explained difference, and ends at the other report's total.
Can I compare on more than one column?: Yes. Join on a combined key, such as region and month, to locate differences in one pass.