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

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.


Download Practice Workbook

You can download the practice workbook from here:


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

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.

Dataset for Excel Add Or Subtract Based On Cell Value


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)

Use of IF Function to Add Or Subtract Based On Cell Value in Excel

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.

Excel Add Or Subtract Based On Cell Value

Read More: Adding and Subtracting in Excel in One Formula (4 Easy Ways)


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.

Applying IF & SUM Functions to Add Or Subtract Based On Cell Value

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.

Result of Doing Excel Add Or Subtract Based On Cell Value

Read More: How to Subtract Sum of Several Cells from Fixed Number 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.

Using Paste Special Feature to Add Or Subtract Based On Cell Value in Excel

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.

  • 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 (6 Ways)


Practice Section

Now, you can practice the explained method by yourself.

Practice Section to Excel Add Or Subtract Based On Cell Value


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. You can visit our website Exceldemy to learn more Excel-related content. Please, drop comments, suggestions, or queries if you have any in the comment section below.


Related Articles

Musiha

Musiha

I am Musiha, graduated from Naval Architecture and Marine Engineering Dept., BUET, currently residing in Dhaka, Bangladesh. And my passion is to grow up my skillsets with industry demands.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo