How to Add or Subtract Based on Cell Value in Excel (3 Ways)

The sample dataset has 2 columns: Month, and Sales. We added a few conditions in a smaller table below it.

Method 1 – Use of IF Function to Add or Subtract Based on Cell Value

If the sales for a month go above $22,000, we’ll apply the increment from C14 to the base salary from C13. Otherwise, we’ll apply the decrement from C15 to the base from C13.

Steps:

Use this formula in the D5 cell.

=IF(C5>22000,$C$13+$C$14,$C$13-$C$15)

Formula Breakdown

The IF function returns the result which will fulfill a given condition.

C5>22000 denotes a logical test. The function will test whether the Sales from C5 are greater than 22000.
$C$13+$C$14 adds C13 cell value to the C14 cell value. The formula will apply this result if the test is TRUE.
$C$13-$C$15 subtracts the C15 cell value from the C13 cell value. The formula will apply this result if the test is FALSE.

Hit Enter.

You will see the Salary for January.

Drag the Fill Handle icon to AutoFill the corresponding data in the rest of the cells D6:D11. You can also double-click on the Fill Handle icon.

You will get all the salary for all the given months.

Method 2 – Applying IF and SUM Functions in Excel

If the sales for a month go above $22,000, we’ll apply the increments from C14 and C15 to the base salary from C13. Otherwise, we’ll apply the decrements from C16 and C17 to the base from C13.

Steps:

Insert this formula in the D5 cell.

=IF(C5>22000,SUM($C$13:$C$15),SUM($C$13,$C$16:$C$17))

Press Enter.

Formula Breakdown

The IF function returns the result which will fulfill a given condition.

C5>22000 denotes a logical test. The function will test whether the Sales is greater than 22000. The formula goes to the first result for TRUE, and the second result for FALSE.
SUM($C$13:$C$15) will add the C13:C15 cells.
SUM($C$13,$C$16:$C$17) will add C13, C16, and C17 cells. Note that the decrements contain negative signs (-).

Drag the Fill Handle icon to AutoFill the corresponding data in the rest of the cells D6:D11. You can also double-click on the Fill Handle icon.

Method 3 – Using the Paste Special Feature to Add or Subtract Based on Cell Value

Steps:

Select the data. We have selected B5:C11 cells.
From the Data tab, go to the Sort & Filter menu, then choose the Sort option.

A dialog box named Sort will appear.

Choose Sales as Sort by and Order as Smallest to Largest.
Press OK.

You will see the following sorted data.

We’ve added the textual version of the condition to cell C16.

Press Ctrl + C and Ctrl + V to copy and paste the basic salary from C13 to D5.

Drag the Fill Handle icon to AutoFill the corresponding data in the rest of the cells D6:D11. You may need to affix the reference.

Copy the C15 cell value.
Select those cells in which the basic salary will be decreased (since we sorted the values, there should be a cut-off point) and right-click on the selection.
Choose Paste Special from the Context Menu.

Another dialog box named Paste Special will appear.

Select All from the Paste option.
Choose Subtract from the Operation menu.
Press OK.

You will get the Salary where Sales did not fulfill the conditions.

Copy the C14 cell value.
Select the cells whose basic salary will be increased and right-click on the selection.
Choose Paste Special from the Context Menu.