Check Percentages Sum to 100%

Excel Formulas › Auditing & Error-Proofing

All versionsSUM

Allocations, weights, and splits must total 100%. A rounded sum check confirms they do — catching the missing or duplicated slice that throws off a budget, a grade weighting, or a commission plan.

Quick formula: do the shares add up to 100%?

=ROUND(SUM(percent_range), 4) = 1

Sum the percentages; if the rounded total equals 1 (100%), the allocation is complete and balanced.

Functions used (tap for the full reference guide):

SUM ROUND

The example

45% + 30% + 25% = 100%.

	A	B
1	Check	Result
2	Sum = 100%?	TRUE

The formula

The formula:

=ROUND(SUM(percent_range), 4) = 1 // sum of shares = 100%

How it works

How it works:

SUM(percent_range) totals the allocation percentages.
Compare to 1 (since 100% = 1) after ROUND to absorb rounding noise.
Show the gap — 1 - SUM — so an off-allocation is easy to fix.
Round to 4 decimals so 33.33% × 3 = 99.99% doesn’t falsely fail (decide your tolerance).

Mind the rounding tolerance. Three equal thirds entered as 33.33% sum to 99.99%, not 100%, and a strict =1 test fails. Decide whether that’s acceptable: round the sum to 2–4 decimals for a forgiving check, or require exact entry (33.34% on one) for a strict one. Surface the gap so the choice is visible, not silently swallowed.

Try it: interactive demo

Live demo

Percentages (comma-separated).

Sum ·

Variations

Show the gap

How far off:

=1 - SUM(percent_range)

Pass/fail message

Readable:

=IF(ROUND(SUM(p),4)=1, "Balanced", "CHECK: " & TEXT(1-SUM(p),"0.0%"))

As whole numbers

If entered 45 not 0.45:

=SUM(percent_range) = 100

Pitfalls & errors

Decimal vs whole. Compare to 1 if percents are 0.45; to 100 if entered as 45.

Rounding tolerance. Equal thirds sum to 99.99% — round the check to match your policy.

Show the gap. A boolean hides the size; display 1 − SUM.

Practice workbook

📊

Download the free Check Percentages Sum to 100% practice workbook

A percentage-sum sheet with the gap, message, and whole-number variants, plus 4 challenges with answers. No sign-up required.

Frequently asked questions

How do I check percentages sum to 100% in Excel?

Use =ROUND(SUM(percent_range), 4) = 1 (percentages as decimals), or = 100 if entered as whole numbers.

Why does 33.33% × 3 fail the check?

It sums to 99.99%, not 100%. Round the sum to your tolerance, or require exact entry like 33.34% on one share.

How do I see how far off the allocation is?

Show the gap: =1 - SUM(percent_range).

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

Function references: SUM ROUND