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

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.

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:

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

❸ Press the ENTER button.

🔎 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:

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

❸ Press the ENTER button.

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

1. Exact Match

• Sum for the matched results

Use formula:

• Sum excluding the matched results

Use formula:

2. Partial Match

• Sum for the matched results

Use formula:

• Sum excluding the matched results

Use formula:

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

❸ Press the ENTER button.

🔎 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: 

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

Use formula: 

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: 

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

Use formula: 

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

Use formula:

4. To sum values having at least 1 character present

Use formula: 

5. To sum values for empty cells

Use formula: 

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

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

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:

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

1. Sum values for current date use

2. Sum values before current dates use

3. Sum values after current dates use

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

Or

Both return the same result.

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.