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.
Read More: Adding and Subtracting in Excel in One Formula
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.
Read More: How to Add and Subtract Multiple Cells in Excel
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.
- Then, choose Add from the Operation menu.
- Lastly, press OK.
So, you will get the Salary for Sales that fulfill the conditions.
Read More: How to Add and Subtract in One Cell in Excel
Practice Section
Now, you can practice the explained method by yourself.
Download Practice Workbook
You can download the practice workbook from here:
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.
Related Articles
- How to Add and Subtract Columns in Excel
- How to Add and Subtract Dates in Excel
- How to Add and Subtract Time in Excel
- How to Calculate Time Difference in Excel VBA
<< Go Back to Excel Add and Subtract | Calculate in Excel | Learn Excel
Get FREE Advanced Excel Exercises with Solutions!
