Check the Total Ties to the Detail

Excel Formulas › Auditing & Error-Proofing

All versionsSUM

A summary total should equal the sum of its detail rows. A tie-out check — the difference rounded to zero — catches a dropped row, a typo, or a stale summary before it ships.

Quick formula: does the summary equal the detail?
=ROUND(summary_total - SUM(detail_range), 2) = 0
Subtract the detail sum from the stated total; if the rounded difference is zero, they tie.

Functions used (tap for the full reference guide):

SUMROUND

The example

Reported $48,000 vs detail rows.

AB
1CheckResult
2Total = SUM(detail)?TRUE

The formula

The formula:

=ROUND(summary_total - SUM(detail_range), 2) = 0 // summary − detail = 0

How it works

How it works:

  1. SUM(detail_range) totals the underlying rows; compare to the stated summary.
  2. Return the difference (not just TRUE/FALSE) so you can see how far off it is.
  3. Always ROUND the difference before testing equality to dodge floating-point noise.
  4. Place the check next to the total so a mismatch is impossible to miss.

Tie-out checks are cheap insurance. A one-cell =ROUND(total - SUM(detail), 2) beside every reported figure catches the classic errors — a row added below the SUM range, a hard-typed total someone forgot to update, a deleted line — the moment they happen. Show the difference, conditionally formatted red when non-zero, and the model audits itself.

Try it: interactive demo

Live demo

Stated total and detail values.

Ties?

Variations

Show the difference

Variance:

=summary_total - SUM(detail_range)

Per-category tie

Subtotal vs detail:

=ROUND(subtotal - SUMIF(cat, this_cat, amt), 2) = 0

Pass/fail flag

Readable:

=IF(ROUND(total-SUM(detail),2)=0, "Ties", "CHECK")

Pitfalls & errors

ROUND first. Compare the rounded difference to 0, not the raw values.

Range coverage. A row added outside the SUM range silently breaks the tie — that’s what the check catches.

Show the gap. A boolean hides the size of the error; display the difference.

Practice workbook

📊
Download the free Check the Total Ties to the Detail practice workbook
A tie-out sheet with the difference, per-category, and flag variants, plus 4 challenges with answers. No sign-up required.

Frequently asked questions

How do I check a total ties to the detail in Excel?
Use =ROUND(summary_total - SUM(detail_range), 2) = 0. Show the difference to see how far off it is.
Why does a total quietly stop matching?
Often a row was added outside the SUM range, or the total was hard-typed and not updated. The tie-out check catches both.
How do I tie each category?
Compare subtotal to SUMIF of its rows: =ROUND(subtotal - SUMIF(cat, this_cat, amt), 2) = 0.

Stop fighting formulas. Learn them in a day.

This recipe is one of hundreds of real-world formulas we teach. Our Excel Formulas & Functions class covers lookups, logic, text, and dynamic arrays hands-on — live in Dallas–Fort Worth, Houston, Austin, Oklahoma City, Denver, or online.

See the Formulas & Functions Class

Related formulas: Cross-foot check · Budget vs actual variance · Round to cents

Function references: SUMROUND