keepcalculator

06 Numbers and Powers

Data Validation Don’t Exceed Total: Fix & Rules

Card No. 06·75566 · Updated · Standard formula, shown below

The calculator on this card is switched off while it is checked. The formula is below, and the worked example shows how to do the sum by hand.

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 B2 instead of $B$2 makes 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

  1. Select the validated cells (e.g., B2:B10).
  2. Go to Data > Data Validation (Excel) or Data > Data validation (Sheets).
  3. Under Allow, choose Custom (Excel) or Custom formula is (Sheets).
  4. Enter the correct formula: =SUM($B$2:$B$10)<=$B$12 (adjust ranges to your sheet).
  5. Ensure the total cell (B12) is a constant or a formula that does not include the validated cells.
  6. 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

The whole Numbers and Powers drawer →