Prevent Cells from Summing to a Negative Value or Vice Versa

Add to Favorites
Author: | Edits: don

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

51c429ec032d85b8511e11f007b2b17d.png

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.

563d43969fdd09b382a521dd0e643d40.png

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.
    8679f0b4db18e79328901209bb0eccab.png
  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.
    ec8e308e1767a487541b306ff6953f9f.png
  4. Under where it says Allow: select Custom
    90c7da57c65eecadf90060d6e0f38b1a.png
  5. Under where it says Formula: input the formula that we copied in Step 1.
    a0de8653c1f95b2de0e807ce2dbacc65.png
  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.

b1e1d2857cfe1de0d67d71fbde7b343f.jpg

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.

16dde7f30aecb76f8d7ef7a46bedd7f1.png

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.

Question? Ask it in our Excel Forum


Excel Function: SUM()
Downloadable Files: Excel File

Similar Content on TeachExcel
Pop-Up Message Box When a Cell Reaches a Certain Value or Contains Certain Text
Macro: This macro will display a message box in excel when a cell reaches a certain value or cont...
Make Negative Numbers Positive in Excel and Vice Versa
Tutorial: I will show you a few ways to change negative numbers to positive numbers and back again ...
Determine if Cells Contain a Specific Value in Excel
Tutorial: Find if a cell or range of cells contains a specific value in Excel. This method can be us...
Automatically Lock Certain Cells in Excel using a Macro
Tutorial: This macro allows you to have a cell automatically locked after a user enters something in...
Enter a Constant Value in a Defined Name in Excel - Text, Numbers, Formulas, Etc.
Tutorial: In Excel you can store values in Defined Names.  Often people use a Defined Name to refe...
Change Specific Text within a Cell in Excel
Tutorial: Change or replace text in a cell with other text - you can replace a single character, num...