Excel DATA VALIDATION using SUM Function

Data Validation in Excel allows you to create custom rules catered to your requirements. You can create a data validation using the SUM function to allow values, which added together, does not exceed a preset total. In this tutorial, you will learn how to create a data validation to only allow values that don’t exceed a set sum.

Data Validation to Only Allow Values That Don’t Exceed a Set Sum

You can create a data validation rule to only allow values that don’t exceed a set sum using the SUM formula. The following example uses a monthly budget data set. Column A has the item names. To allow values in column B so that the total doesn’t exceed $4,000:

  1. Select cell B2. Drag the selection till B7 with your mouse.
  2. Go to Data > Data Validation. 
  3. From the drop-down in Allow, select Custom
  4. In the Formula box, write the formula =SUM($B$2:$B$7) < 4000. 
  5. Click OK.

This will only allow values in column B so that the total does not exceed $4,000. The rule is triggered once a user adds or modifies a value. Here, the formula returns=SUM($B$2:$B$7) < 4000 FALSE as long as entries in B2:B7 totals to a value less than $4,000. The sum covers an absolute reference in cells B2 to B7 to stop the reference from changing for each cell the validation is applied to. SUM is used to sum this fixed range and check if the total is less than or greater than $4,000. Once you enter a new number, the validation is triggered. As long as the total of the values in B2 to B7 is less than $4,000, the validation passes. If any value makes the total to exceed $4,000, validation fails and displays an error message. You can modify this and show your own custom message. To do this:

  • After completing step 4, go to Error Alert.
  • In the Error Message box, set the message to The current input causes the sum to exceed $4,000. Please enter a smaller number.
  • Click Ok.

Every time the validation fails, Excel will now show your custom error message.

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