How to SUM with IF Condition in Excel (6 Suitable Examples)

Get FREE Advanced Excel Exercises with Solutions!

Sometimes we need to sum up values based on criteria in Excel. To meet up this requirement, Excel has embedded itself with a built-in function called the SUMIF function. The SUMIF function enables us to sum values in Excel with the if condition. With that keeping in mind, in this article, you will learn how to use the SUMIF function, to sum up, values with the if condition in Excel.

sum if condition in excel

Take a look at the overview. You’ll learn more in the next section of the article.


Download the Practice Workbook

You are recommended to download the Excel file and practice along with it.


The SUMIF Function: an Overview

This function enables us to sum up a range of cells with a condition.

Syntax

SUMIF(range,criteria,sum_range)

Arguments

  • range: This field is mandatory. It refers to the range of cells that include the criteria.
  • criteria: This field is also mandatory. It refers to the condition that must be satisfied.
  • sum_range: This is an optional requirement. It refers to the range of cells to add if the condition is satisfied.

6 Ways to SUM with IF Condition in Excel

In this article, we will be using a sample product price list as a dataset to demonstrate all the methods. So, let’s have a sneak peek of the dataset:

SUM with IF Condition in Excel dataset

So, without having any further discussion let’s dive straight into all the methods one by one.


1. Use SUM with If Condition For Different Comparison Criteria in Excel

Let’s sum up those prices greater than $40. To do so,

❶ Select cell C14.

Type the formula

=SUMIF(C5:C12, ">40")
within the cell.

❸ Press the ENTER button.

Sum If Different Comparison Criteria is Required in Excel

🔎 Into the Formula

Within the criteria field, we’ve inserted  “>40“, where the “>” operator filters out all the prices greater than $40. As a whole the formula above sums up all the prices greater than $40. There are more operators like the “>” that are listed below:

Operator Condition
> Sum if greater than
< Sum if less than
= Sum if equal to
<> Sum if not equal to
>= Sum if greater than or equal to
<= Sum if less than or equal to

2. SUM If Various Text Criteria Appear in Excel

We can use the SUMIF function, to sum up, values based on text matching. For instance, we will sum up the prices for exact matching with the product called “CPU”. To make it done,

❶ Select cell C14.

Type the formula

=SUMIF(B5:B12, "CPU", C5:C12)
within the cell.

❸ Press the ENTER button.

Sum If Various Text Criteria Appears

To sum up, values based on matches can be split into 2 basic categories:

1. Exact Match

  • Sum for the matched results

Use formula:

=SUMIF(B5:B12, "CPU", C5:C12)
  • Sum excluding the matched results

Use formula:

=SUMIF(B5:B12, " <>CPU", C5:C12)

2. Partial Match

  • Sum for the matched results

Use formula:

=SUMIF(B5:B12, "*CPU*", C5:C12)
  • Sum excluding the matched results

Use formula:

=SUMIF(B5:B12, " <>*CPU*", C5:C12)

3. Excel SUMIF Function Condition with Numerous Comparison Operators & Cell Reference

The SUMIF function enables us to build a search box and execute the sum operation based on values input into the search box. For instance, we want to calculate the total prices of all the products excluding the item “Monitor”. Now let’s go through the steps to implement it:

❶ Select cell C15.

Type the formula

=SUMIF(B5:B12,"<>"&C14, C5:C12)
within the cell.

❸ Press the ENTER button.

Excel SUMIF Function Condition with Numerous Comparison Operators & Cell Reference

🔎 Into the Formula

Within the criteria field, we’ve used “<>”&C14, where the “<>” is responsible for not taking into account what has been pointed out in cell C14.

1. To sum up prices for the items excluding “Monitor”

Use formula: 

=SUMIF(B5:B12,"<>"&C14, C5:C12)

2. To sum up prices for the item “Monitor”

Use formula: 

=SUMIF(B5:B12,C14, C5:C12)

4. Use Excel SUMIF Function Condition with Wildcard Symbols

If you are interested in summing up values based on partial matches then you can use one of the two wildcard symbols:

  • Asterisk (*) – represents any number of characters.
  • Question mark (?) – represents a single character in a specific position

4.1 Partial Matches with Wildcards

1. To sum values that begin with the word “Mouse”

Use formula: 

=SUMIF(B5:B12,"Mouse*", C5:C12)

2. To sum values that end with the word “Mouse”

Use formula: 

=SUMIF(B5:B12,"*Mouse", C5:C12)

sum if condition in excel

3. To sum values for “Mouse” being present at any position

Use formula:

=SUMIF(B5:B12,"*Mouse*", C5:C12)

4. To sum values having at least 1 character present

Use formula: 

=SUMIF(B5:B12,"?*", C5:C12)

5. To sum values for empty cells

Use formula: 

=SUMIF(B5:B12,"*", C5:C12)

4.2 SUM Values Having a Specific Number of Characters

Suppose you want to sum up values that have X characters long words in their corresponding cell. For the convenience of execution, let’s calculate the total price for 3 characters long products:

Type the formula

=SUMIF(B5:B12, "???", C5:C12)
within cell C14 and hit the ENTER button.

Here the number of question mark (?) signs determines the number of characters to search into.

Use Excel SUMIF Function Condition with Wildcard Symbols


5. Excel SUMIF Function with Date Condition

Say, we want to find out the total product sales on a particular date. To find it out, all we can do is simply insert the date into the criteria field. Here’s the formula:

=SUMIF(B5:B12,"9/1/2020",D5:D12)

SUMIF Function with Date Condition

We can do more with the SUMIF function for dates. Such as,

1. Sum values for current date use

=SUMIF(B5:B12, "TODAY()",D5:D12)

2. Sum values before current dates use

=SUMIF(B5:B12, "<"&TODAY(),D5:D12)

3. Sum values after current dates use

=SUMIF(B5:B12,">"&TODAY(),D5:D12)

6. Sum If Blank Cells Correspond to the Values in Excel

In this section, we will sum up only those prices whose corresponding dates are missing. To do so, you can use formula

=SUMIF(B5:B12,"=",D5:D12)

Or

=SUMIF(B5:B12,"",D5:D12)
 within cell D14.

Both return the same result.

Blank Cells Correspond to the Values


Things to Remember

📌 Be aware of the syntax of the SUMIF function.

📌 Carefully handle the range field inside the formula.

📌 Do not insert arrays in range or sum_range fields.

📌 The size of the range and the sum_range should be the same.


Conclusion

To sum up, we have discussed 6 ways, to sum up, values with the if condition in Excel. You are recommended to download the practice workbook attached along with this article and practice all the methods with that. And don’t hesitate to ask any questions in the comment section below. We will try to respond to all the relevant queries asap. And please visit our website ExcelDemy to explore more.

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.
Mrinmoy Roy
Mrinmoy Roy

Mrinmoy Roy, a dedicated professional with a BSc in Electronics and Communication Engineering from Khulna University of Engineering & Technology, Bangladesh, brings over two years of expertise to the ExcelDemy project. As a prolific contributor, he has authored around 180 articles, showcasing his deep knowledge and passion for Microsoft Excel, Data Analysis, and VBA. His unwavering commitment to continuous learning, combined with versatile skills, renders him well-suited for roles in data management and spreadsheet solutions. He has interest... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo