Excel Office

Excel How Tos, Tutorials, Tips & Tricks, Shortcuts

Excel Data validation don’t exceed total

Using the example below, this tutorial shows how to create Data validation don’t exceed total in Excel.




To allow only values that don’t exceed a set sum, you can use data validation with a custom formula based on the SUM function. In the example shown, the data validation applied to B5:B9 is:


How this formula works

Data validation rules are triggered when a user adds or changes a cell value.

Worked Example:   IF with boolean logic in Excel

In this case, we need a formula that returns FALSE as long as entries in C6:C9 sum to a total equal to or below 1000. We use the SUM function to sum a fixed range and then simply compare the result to 1000 using less than or equal to. Note the range C6:C9 is entered as an absolute reference to prevent the reference from changing automatically for each cell that data validation is applied to.

Worked Example:   How to reference named range different sheet in Excel

Each time a number is entered, the validation is triggered. As long as the sum remains less than 1000, validation succeeds. If any entry causes the sum C6:C9 to exceed 1000, validation fails.

Worked Example:   Count and Sum Functions in Excel

Note: Cell references in data validation formulas are relative to the upper left cell in the range selected when the validation rule is defined, in this case B5.

Leave a Reply

Your email address will not be published. Required fields are marked *