How to Subtract in Excel Based on Criteria (3 Suitable Examples)

Get FREE Advanced Excel Exercises with Solutions!

This article will discuss methods to subtract excel data based on different criteria. While working with Microsoft Excel, we subtract cell values using the basic minus formula or the SUM function. The basic subtraction formula we use in excel is ‘Cell1-Cell2‘.

However, the basic formula is not enough while doing complex subtraction. In such cases, we have to subtract cell values based on specific criteria. Let’s go to the article to see some examples of criteria based on which we will deduct values.


How to Subtract in Excel Based on Criteria: 3 Suitable Examples

1. Subtraction of Excel Data Based on Empty Cell

Suppose we have the below dataset containing empty cells randomly.

3 Examples to Subtract in Excel Based on Criteria

Now based on these blank cells I will subtract cell values from one another using the IF function. Follow the below steps to perform the task.

Steps:

  • First, type the below formula in Cell F5 and press Enter.
=IF(C5<>"",B5-C5,IF(D5<>"",B5-D5,IF(E5<>"",B5-E5,"")))

3 Examples to Subtract in Excel Based on Criteria

  • Upon entering the formula we will get the below result. Then use the Fill Handle (+) tool to copy the formula over the range F5:F10.

  • Finally, we will get the below output.

🔎 How Does the Formula Work?

Here the above formula is nested with two IF functions

  • IF(E5<>””,B5-E5,””)

The above formula checks whether the value Cell E5 is equal to blank or not. Here the formula returns:

{20}

  • IF(D5<>””,B5-D5,IF(E5<>””,B5-E5,””))

Then this part of the formula checks if Cell D5 is empty or not. As the Cell D5 is not empty, the formula returns:

{15}

  • IF(C5<>””,B5-C5,IF(D5<>””,B5-D5,IF(E5<>””,B5-E5,””)))

At last, the formula checks whether Cell C5 is blank or not. Here Cell C5 has a value, so the output is:

{10}

Read More: Subtraction for Whole Column in Excel


2. Excel IF Function to Subtract When Cell Content Is Greater Than Specific Number

Suppose we have the below dataset containing numbers in two lists. Now we will subtract the values of Data 1 from Data2, where the number of Data 1 is greater than 50.

3 Examples to Subtract in Excel Based on Criteria

Let’s follow the below steps to get the task done.

Steps:

  • Initially, type the below formula in Cell D5.
  • Next hit Enter.
=IF(B5>50,C5-B5,B5)

3 Examples to Subtract in Excel Based on Criteria

  • Consequently, we will get the below output.

From the above result, we can see that when the values of Data 1 are greater than 50, the above formula subtracts the number of Data1 from those of Data 2. Otherwise, the formula returns the value of Data 1.

Read More: How to Subtract Two Columns in Excel


3. Subtract If One Cell Value Is Greater Than Another

In this method, I will compare cell values, and based on that comparison I will subtract values from specific cells. Let’s assume we have the dataset below, which contains data in three different columns.

3 Examples to Subtract in Excel Based on Criteria

Now, follow the below instructions to subtract cell values based on the abovementioned criteria.

Steps:

  • First, type the following formula in Cell E5 and press Enter.
=IF(B5>C5,D5-B5,D5-C5)

3 Examples to Subtract in Excel Based on Criteria

  • Upon entering the formula and Fill Handle tool, excel returns the below result.

Here, the IF function checks whether the value of Cell B5 is greater than Cell C5 first. Then, if the first condition is true, the formula subtracts Cell B5 from Cell D5. If not, the formula subtracts the value of Cell C5 from the one of Cell D5.

Read More: How to Create a Subtraction Formula in Excel


Download Practice Workbook

You can download the practice workbook that we have used to prepare this article.


Conclusion

In the above article, I have tried to discuss several methods to subtract based on criteria in excel elaborately. Hopefully, these methods and explanations will be enough to solve your problems. Please let me know if you have any queries.


Related Articles


<< Go Back to Subtract in Excel | Calculate in Excel | Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Hosne Ara
Hosne Ara

Hosne Ara is a materials and metallurgical engineer who loves exploring Excel and VBA programming. To her, programming is like a time-saving superhero for dealing with data, files, and the internet. She's skilled in Rhino3D, Maxsurf C++, MS Office, AutoCAD, and Excel & VBA, going beyond the basics. With a B. Sc. in Materials and Metallurgical Engineering from Bangladesh University of Engineering and Technology, she's shifted gears and now works as a content developer. In this role, she... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo