Prevent Cells from Summing to a Negative Value or Vice Versa

How to prevent a range of cells from adding up to a negative value, or preventing the same cells from adding to a positive value.

For instance, if cell A1 has 10 and A2 has -15, the sum of these cells would be negative 5, and, below, you will learn how to prevent this from happening.

To do this, we will use a custom formula combined with Data Validation.

Sections:

Prevent Range of Cells from Summing to a Negative Value

Prevent Range of Cells from Summing to a Positive Value

Notes

Prevent Range of Cells from Summing to a Negative Value

In order to do this, we will use Data Validation in Excel.

For this example, we want to make sure that the values entered into cells A1, A2, & A3 will always sum to a positive number.

Create a Custom Formula

Data Validation will be used to prevent the user from entering the wrong value into a range of cells, but we need to first give the system the criteria that it needs to know when to prevent the user from doing that. For this, we use a custom formula.

This custom formula needs to return TRUE or FALSE. It must return FALSE when the sum of a range is negative and TRUE when it is positive.

=SUM($A$1:$A$3)>0

The comparison operator > is what makes this formula logical and is what checks if the result of the SUM function is greater than zero or not and, therefore, returns TRUE or FALSE.

Change the numbers to sum to greater than zero to test the formula and, once you are sure that it works correctly, move on to the next step.

Important - make sure that the cell references for the range are absolute references (with dollar signs in front of them) or the next steps won't work correctly. This is because we need to copy this formula down for the entire range of values for the Data Validation and we don't want the range references to update or change when we do this.

Apply the Data Validation

Once you have the custom formula, you can continue to apply the Data Validation. This must be applied to all cells in the range.

1. Copy the formula that we made in the last step.
2. Select the cells that are in the desired range.
   
3. Hit the keyboard shortcut Alt + D + L to go to the Data Validation window (Data tab > Data Validation). You should now see a new smaller window.
   
4. Under where it says Allow: select Custom
   
5. Under where it says Formula: input the formula that we copied in Step 1.
   
6. Hit OK and we are done and ready to test it out!

To test it out, clear the values in the range and try to enter numbers that sum to a negative value. It doesn't matter if you fill-in all cells or just one cell; if the sum of the values in the range equals a negative number, Excel should throw an error and prevent the user from entering the values.

From now on, the only time it won't throw an error, is when the values in the cells sum to a postive number or when all of the cells are left empty.

Prevent Range of Cells from Summing to a Positive Value

Follow the last example, but change the formula from this:

=SUM($A$1:$A$3)>0

to this:

=SUM($A$1:$A$3)<0

The difference is that > became <

Every other step is exactly the same as in the previous section.

Notes

This is actually a relatively simple concept, but it requires combining a few techniques that are not often all used together: basic functions, comparison operators, custom Data Validation formulas. However, each piece of the equation is not difficult on its own. To learn more about these features, visit our tutorials on them:

Data Validation

Comparison Operators

Functions in Excel

Make sure to download the attached sample file to work with the above examples in Excel.