Data validation “don’t exceed total” errors occur when the sum of values in a set of validated cells is forced to stay at or below a single total cell, and your entry would break that limit. The fix is to adjust the validation rule’s formula (or the total cell) so the new value is allowed, or to change the data so the total isn’t exceeded.
This is a common Excel and Google Sheets constraint used for budgets, task hours, or inventory caps. Below, we explain how it works, why it fails, and exactly how to resolve it.
Background: Why This Validation Exists
Spreadsheet validation prevents users from entering values that would break a business rule. The “don’t exceed total” rule is a custom formula-based validation, often written as =SUM($B$2:$B$10)<=$B$12 in Excel’s Data Validation settings. It’s not a built-in option; you create it with a formula. The same logic applies in Google Sheets using Custom formula is.
Common use cases: allocating project hours across tasks, tracking expense categories against a budget, or limiting item quantities in an order form. The rule checks the cumulative sum after each entry, not just the individual cell.
How the Validation Formula Works
The formula compares the sum of all input cells to a fixed total cell. For example, if cells B2:B10 hold task hours and B12 is the cap (say 40), the rule is =SUM(B2:B10)<=B12. When you type a value in any of those cells, Excel recalculates the sum and only accepts the entry if the new total is ≤ 40.
If you try to enter 10 when the current sum is 35, the validation fails because 35+10=45 > 40. The error message you see is the one you wrote in the validation settings, or the default “This value doesn’t match the data validation restrictions.”
Common Causes of the Error
- Typo in the formula range: The SUM range may include extra cells or miss some, causing the total to be higher than intended.
- Total cell is a formula: If the total cell itself is calculated (e.g.,
=SUM(B2:B10)), your validation may be comparing against itself, creating a circular reference. - Absolute vs. relative references: Using relative references like
B2instead of$B$2makes the rule apply differently to each cell, often breaking the sum check. - Hidden rows or filtered data: SUM includes hidden rows unless you use SUBTOTAL, so the actual total may be larger than what you see.
- Rounding: If your values have decimals, the sum may exceed the limit by a tiny fraction, triggering the error.
How to Fix the “Don’t Exceed Total” Error
- Select the validated cells (e.g., B2:B10).
- Go to Data > Data Validation (Excel) or Data > Data validation (Sheets).
- Under Allow, choose Custom (Excel) or Custom formula is (Sheets).
- Enter the correct formula:
=SUM($B$2:$B$10)<=$B$12(adjust ranges to your sheet). - Ensure the total cell (B12) is a constant or a formula that does not include the validated cells.
- Click OK. Now test by entering a value that keeps the sum ≤ total.
If you still get the error, check that the total cell is not accidentally part of the SUM range. Also, verify that no cell in the range has a pre-existing value that already exceeds the limit.
Alternatives to a Single Total Cell
Instead of a fixed total, you can use a helper column that tracks running totals, or use a formula that subtracts the current entry from the remaining allowance. For example, allow a value only if it is ≤ remaining budget: =B2<=($B$12-SUM($B$2:$B$10)+B2) — but this is more complex. A simpler alternative is to use a dropdown list of pre-approved values, or to apply conditional formatting to highlight overages instead of blocking entry.
For multi-row data entry, consider using a table with structured references (e.g., =SUM(Table1[Amount])<=Table1[[#Totals],[Budget]]), which auto-adjusts as rows are added.
Safety and Limitations
Data validation is not a security feature; a user can copy-paste values to bypass it, or clear validation entirely. It also does not prevent errors if the total cell is edited manually. For critical financial or safety-related limits, use a more robust system like a database constraint or a script that runs on edit.
Also note that validation only checks the sum at the moment of entry. If you later change a value in the range, the validation does not re-check the previous entries. You may end up with a total that exceeds the limit without any error, because the last entry was valid at the time.
Frequently Asked Questions
Why does my data validation allow a value that makes the total exceed the limit?
This happens if the validation formula uses relative references incorrectly, or if the SUM range does not include all cells. For example, if you entered =SUM(B2:B10)<=B12 but the cell you are editing is B2, the rule works. But if you used =SUM(B2:B10)<=B2, it compares the sum to the current cell, not the total. Check your formula and use absolute references for the total cell.
Can I set a validation that only checks the total after all entries are done?
Yes, but not with standard data validation. You can use a macro or a script (Google Apps Script) that runs on edit and checks the total, reverting the change if it exceeds. Alternatively, use conditional formatting to highlight the total cell in red when over, and rely on the user to correct it.
How do I apply the same validation to multiple non-adjacent cells?
Select all the cells (hold Ctrl while clicking) and apply the validation. The formula should use absolute references for the SUM range and the total cell. For example, if you select A1, C1, and E1, the formula =SUM($A$1,$C$1,$E$1)<=$G$1 works, but a range like $A$1:$E$1 would include B1 and D1, which may not be intended.
Does this validation work in Google Sheets the same way?
Yes, but the menu is different. In Google Sheets, go to Data > Data validation, choose Custom formula is, and enter the same formula. The error message is set in the “Show validation help text” option. The logic is identical.
Why is my total cell showing a circular reference warning?
If your total cell is =SUM(B2:B10) and your validation formula in B2:B10 also references that total cell, you create a circular reference. The validation formula should not include the total cell in its own calculation. Use a separate constant cell for the limit, or use a formula that excludes the total cell, like =SUM($B$2:$B$10)<=100 (hardcoded).
Related
Formula used
in t | Total allowed | number | 100 | 0 | 1000000 in d | Current sum | number | 80 | 0 | 1000000 in v | New value | number | 25 | 0 | 1000000 out sum | New total | t | d + v | 2 out remain | Remaining | t | t - d | 2 out ok | Allowed? | - | IF(v
Each line is one input (in, sel, list) or one result (out: label, unit, formula, decimals). How the calculators work
Method: the standard formula for this calculation, rounded as each result states. See how the calculators work or report an error.
More cards in this drawer
- Data Center Security: Encryption, Backups, Access
- Best Mini PCs for Gaming: Top Picks
- Phone Buttons: Clever Ways to Use Them
- Resonance Solstice Codes: What They Are
- Learned Phone Sensor Power: What It Really Does5.00 min/km
- Peak Evolution Codes: What They Are & How to Use132