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

Get FREE Advanced Excel Exercises with Solutions!

Occasionally you may need to Add or Subtract based on cell value in Excel. So, if you are looking for how to Add or Subtract based on cell value in Excel then you have come to the right place. In this article, I will demonstrate how to Add or Subtract based on cell value in Excel.

## How to Add or Subtract Based on Cell Value in Excel: 3 Methods

Here, I will describe 3 suitable methods to Add or Subtract based on cell values in Excel. In addition, for your better understanding, Iâ€™m going to use a sample dataset.

Moreover, the sample dataset has 2 columns. Those are Month, and Sales. The dataset is given below. If you notice then you will see some additional information given at the bottom of the data.

### 1. Use of IF Function to Add or Subtract Based on Cell Value

You can use the IF function to Add or Subtract based on cell value in Excel. Suppose, you want to know how much the salary will be for all the given months. Because the salary is based on the sales. And, if the sales cross \$22000 then there will be an increment of \$5000 otherwise there will be a decrement of \$4000 from basic salary.

Furthermore, you can modify this function for different types of results according to your preference. The steps are given below.

Steps:

• Firstly, you have to select an empty cell D5 where you want to see the Salary.
• Secondly, you should use the corresponding formula in the D5 cell.
`=IF(C5>22000,\$C\$13+\$C\$14,\$C\$13-\$C\$15)`

Formula Breakdown

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

• Firstly, the C5>22000 denotes a logical test. Where the function will test that either the Sales are greater than 22000 or not.
• Secondly, \$C\$13+\$C\$14 denotes that if the logic is TRUE which means if the cell value of C5 is greater than 22000 then it will do this calculation. In this calculation, the C13 cell value will add to the C14 cell value.
• Finally, \$C\$13-\$C\$15 denotes that if the logic is FALSE which means if the cell value of C5 is less than 22000 then it will do this calculation. In this calculation, the C15 cell value will subtract from the C13 cell value.

• Subsequently, you must press ENTER to get the result.

At this time, you will see the Salary for January.

• After that, you have to drag the Fill Handle icon to AutoFill the corresponding data in the rest of the cells D6:D11. Or you can double-click on the Fill Handle icon.

Finally, you will get all the salary for all the given months.

### 2. Applying IF & SUM Functions in Excel

Again, you can use IF, and SUM functions to Add or Subtract based on cell values in Excel. Moreover, you can modify this function for different types of results according to your preference.

Now, suppose, you want to know how much the salary will be for all the given months. Because the salary is based on the sales. And, if the sales cross \$22000 then there will be an increment of \$5000 including an extra increment of \$2000 otherwise there will be a decrement of \$4000 including an extra decrement of \$1500 from basic salary.

The steps are given below.

Steps:

• Firstly, you have to select an empty cell D5 where you want to see the Salary.
• Secondly, you should use the corresponding formula in the D5 cell.
`=IF(C5>22000,SUM(\$C\$13:\$C\$15),SUM(\$C\$13,\$C\$16:\$C\$17))`
• Thirdly, press ENTER.

At this time, you will see the Salary for January.

Formula Breakdown

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

• Firstly, the C5>22000 denotes a logical test. Where the function will test that either the Sales is greater than 22000 or not.
• Secondly, SUM(\$C\$13:\$C\$15) denotes that if the logic is TRUE which means if the cell value of C5 is greater than 22000 then it will do this calculation. In this calculation, the SUM function will add C13:C15 cells.
• Finally, SUM(\$C\$13,\$C\$16:\$C\$17) denotes that if the logic is FALSE which means if the cell value of C5 is less than 22000 then it will do this calculation. In this calculation, the SUM function will add C13, C16, and C17 cells. As the decrement contains negative signs (-). So this SUM will work as a subtraction.

Now, I will find out the Salary for the other months.

• So, you have to drag the Fill Handle icon to AutoFill the corresponding data in the rest of the cells D6:D11. Or you can double-click on the Fill Handle icon.

Finally, you will get all the salary for all the given months.

### 3. Using Paste Special Feature to Add or Subtract Based on Cell Value

Here, you can use the Paste Special feature to Add or Subtract based on cell values in Excel. Suppose, you want to know how much the salary will be for all the given months. Because the salary is based on sales. And, if the sales cross \$22000 then there will be an increment of \$5000 otherwise there will be a decrement of \$4000 from basic salary. Now, letâ€™s see the steps.

Steps:

• Firstly, select the data. Here, I have selected B5:C11 cells.
• Secondly, from the Data tab >> go to the Sort & Filter menu >> then choose the Sort option.

At this time, a dialog box named Sort will appear.

• Now, choose Sales as Sort by and Order as Smallest to Largest.
• Then, press OK.

Subsequently, you will see the following sorted data.

Here, for sorting you can easily find out which Sales wonâ€™t fulfill the criteria.

• Now, use Excel keyboard shortcuts CTRL+C and CTRL+V to copy and paste the basic salary in the D5 cell.

• Here, you may drag the Fill Handle icon to AutoFill the corresponding data in the rest of the cells D6:D11.

• Again, use the Excel keyboard shortcuts CTRL+C to copy the C15 cell value.
• Then, select those cells in which the basic salary will be decreased and right-click on the Mouse.
• After that, choose Paste Specialâ€¦ from the Context Menu Bar.

At this time, another new dialog box named Paste Special will appear.

• Now, select All from the Paste option.
• Then, choose Subtract from the Operation menu.
• Lastly, press OK.

Now, you will get the Salary which Sales did not fulfill the conditions.

• Again, use the Excel keyboard shortcuts CTRL+C to copy the C14 cell value.
• Then, select those cells whose basic salary will be increased and right-click on the Mouse.
• After that, choose Paste Specialâ€¦ from the Context Menu Bar.

At this time, another new dialog box named Paste Special will appear.

• Now, select All from the Paste option.
• Lastly, press OK.

So, you will get the Salary for Sales that fulfill the conditions.

## Practice Section

Now, you can practice the explained method by yourself.

## Conclusion

I hope you found this article helpful. Here, I have explained 3 suitable methods to Add or Subtract based on cell value in Excel. Please, drop comments, suggestions, or queries if you have any in the comment section below.

## What is ExcelDemy?

ExcelDemy Learn Excel & Excel Solutions Center provides free Excel tutorials, free support , online Excel training and Excel consultancy services for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Musiha Mahfuza Mukta

Musiha Mahfuza Mukta, BSc, Naval Architecture and Marine Engineering, Bangladesh University of Engineering and Technology, has been working with the ExcelDemy project for 1.5 years. She has written over 97+ articles for ExcelDemy. Currently, she is working as the Excel and VBA Content Developer, creates contents, solves user problems, writes articles etc. Her work and learning interest varies from Microsoft Office Suites, and Excel to Data Analysis, and developing Excel Applications with VBA codes.

We will be happy to hear your thoughts

Advanced Excel Exercises with Solutions PDF