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.
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.