< Go Back

Excel DATA VALIDATION using SUM Function

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.

 

Our customers love us!
“The expert was absolutely amazing and stuck with me the whole way through. They were polite, patient, seemed to want to genuinely help me and provided a solution that I would never have managed otherwise. I could not be more thankful for their support and solution. Thank you!” - - Chris T, in California

Leave a Comment

avatar