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.

Prevent Range of Cells from Summing to a Negative Value

Prevent Range of Cells from Summing to a Positive 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.

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.

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

- Copy the formula that we made in the last step.
- Select the cells that are in the desired range.
- 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. - Under where it says
**Allow:**select**Custom** - Under where it says
**Formula:**input the formula that we copied in Step 1. - 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.

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.

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:

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

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...

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...

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...

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...

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...

Tutorial: In Excel you can store values in Defined Names. Often people use a Defined Name to refe...

SUBSTITUTE() Function - 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...

Tutorial: Change or replace text in a cell with other text - you can replace a single character, num...