How to Do Excel Data Validation Using the 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.

Still need some help with Excel formatting or have other questions about Excel? Connect with a live Excel expert here for some 1 on 1 help. Your first session is always free.