Go Back

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. 

Did this post not answer your question? Get a solution from connecting with the expert.

Another blog reader asked this question today on Excelchat:
Here are some problems that our users have asked and received explanations on

I don't know how to condense this formula as there is a limit of characters for one cell and it seems I exceed it.
Solved by A. Q. in 28 mins
I'd like a formula to sum n number of values, that don't appear in a range!
Solved by B. F. in 21 mins
I need C30:C35 not to be able to exceed the figures adjacent row/column G30:G35. 5% 10% 10% 5% 5% I also need the total sum in column C30:C35 to not be able to exceed 20% while only allowing for a maximum of 3 inputs into C30:C35 if possible.
Solved by G. C. in 18 mins

Leave a Comment

avatar