Excel Data Validation restricts users to enter values with a certain condition using custom formulas. If you want to restrict users to enter numeric values in a selected range of cells whose sum or totally do not exceed to a specified sum then you can achieve it using Data Validation custom formula.

In Excel Data Validation to only allow values that don’t exceed a set sum, you need to set a custom formula based on SUM function. For example, if you want to set a budgetary limit for various expenditures where each department is allowed to make expenses that don’t exceed a set sum of **$50,000**. In this case, you need to apply Data Validation to only allow values that don’t exceed a set sum of **$50,000** to a selected range of cells.

Suppose you want to apply this custom rule on cells range **C2:C9** for a specified account head using Data Validation to only allow values that don’t exceed a set sum of $50,000. Excel does not allow users to enter a value that exceeds this budgetary limit of $50,000 in cells C2:C9. The following customer will be used in Data Validation to set up this rule.

`=SUM($C$2:$C$9)<=50000`

This formula serves as a rule that triggers whenever a user enters or edits the value in the selected range of cells. This formula returns **TRUE** as long as values in cells range C2:C9 give a sum that is less than or equal to $50,000. This rule triggers each time a value is entered or edited in C2:C9 using Data Validation to only allow values that don’t exceed a set sum of $50,000. If the user enters values whose sum exceeds the set sum then this rule triggers and custom formula returns **FALSE** and Data Validation does not allow users to enter or edit those values.

For example, when users enter the amount for “Miscellaneous” as $900, the sum of values in C2:C9 exceeds $50,000 and Excel generates a warning message and does not allow the user to enter this amount.

## Leave a Comment