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.

Formula

=SUM(range)<=1000

Explanation

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:

=SUM($C$6:$C$9)<=1000

How this formula works

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

Also See:   Sum bottom n values with criteria 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.

Also See:   Example of Count with Or Criteria 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.

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.

Also See:   SUM function: Description, Usage, Syntax, Examples and Explanation

Leave a Reply

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