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.

**Table of Contents**Expand

## 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

`=SUMIF(C5:C12, ">40")`

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

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)`

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

`=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)`

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

`=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)`

**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)`

**C14**and hit the

**ENTER**button.

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:

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

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)`

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.