## Subscribe for Weekly Tutorials

### BONUS: subscribe now to download our Top Tutorials Ebook!

# 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

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

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

## 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:

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

#### Question? Ask it in our Excel Forum

Macro: This macro will display a message box in excel when a cell reaches a certain value or cont...

Tutorial: I will show you a few ways to change negative numbers to positive numbers and back again...

Tutorial: Find if a cell or range of cells contains a specific value in Excel. This method can be us...

Tutorial: This macro allows you to have a cell automatically locked after a user enters something in...

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

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