The Excel SUMIF function is a mathematical formula that is an advanced version of **SUM function** in excel.

The name in itself clarifies its purpose. SUM + IF meaning to find **SUM** of numbers in cell(s) only **IF **the specified condition is met.

In this tutorial we would learn about how to use the SUMIF function in excel, along with its syntax arguments and examples.

Here we go 😎

**When to Use SUMIF Function in Excel**

The SUMIF formula is a part of the math/trig function group. This formula used to get the total of numbers in the cells based on the fulfillment of a specified condition.

It is important to note that the SUMIF formula only checks for a *single condition* and not more.

It returns the sum of values.

**Syntax and Arguments**

**=SUMIF(range, criteria, [sum_range])**

Below points explain the arguments of the SUMIF function:

**range**– In this argument, specify the cell range in which you want to check the condition.

**criteria**– In this argument, specify the criteria to check in the range. This argument supports condition check by using logical operators like >,<,>=,<=, and =. It also supports the use of wildcard characters i.e. an asterisk (*) and question mark (?) for partial matches of values.

**[sum_range])**– This is an optional argument. In this argument, specify the cell range to sum values from. If you keep it blank, then the formula takes the [sum_range] (third argument) same as the criteria range (first argument).

To reiterate, the SUMIF function only checks for a single criteria. If you want to check for multiple criterias, then use the

SUMIFS excel function.

**Examples of SUMIF Function in Excel**

Let us now learn with the help of examples about how the SUMIF function works in excel.

**Ex. 1 # Sum Values Only If They Are Greater Than Or Less Than A Number**

Suppose you have a list of numbers in excel and you want to find the sum of those numbers or values which are greater than 100.

To do so, use the below formula:

=SUMIF(A2:A12,">100",A2:A12)

As a result, excel would total only those values from the range which are greater than 100.

Note that, any text that you mention in the *criteria *argument must be specified without double quotes (” “). In above example, we have entered the text >100 within double quotes.

Instead of manually typing the criteria text, you may even use reference of a cell. In that case, do not use double quotes. See below image.

In the above example, to join the mathematical operator symbol and the reference cell, I have used the ampersand symbol (&). We may instead use the **CONCATENATE excel function**, like this:

=SUMIF(A2:A12,CONCATENATE(">",D1),A2:A12)

Other mathematical logical operators are:

**Ex. 2 # SUM Values Based on Text**

In the above example we saw how to sum values based on the number criteria. In this example section, we would learn how to sum values based on the text criteria.

**Also Read: **How to Sum Values Based on Criteria

The below image shows the region wise sales made by a company in a year.

Suppose you want to find the total sales made in the West region. To achieve this, use the following formula:

=SUMIF(A2:A12,"West",B2:B12)

As a result, excel returns the value 5032 (which is total sales made in West.

**Ex. 3 # SUM Values Using Partial Match of Text**

Wildcard characters play a very important role in excel when you want to find and/or replace values based on a partial match of text. There are mainly three wildcard characters namely – asterisk (*), question mark (?), and tidle (~).

The below image illustrates the sales made by different companies spread across world. Here, IN – India, CA – Canada, FI – Finland, US – Unites States of America, and SE – Sweden.

Now, to find the total sales made by all Indian company codes (starting with IN), the asterisk wildcard characters will be useful.

Use the following formula:

=SUMIF(A2:A12,"IN*",B2:B12)

In the above example, the criteria “*IN**” (an asterisk after IN) searches for all the texts in range A2:A12 which starts with the word IN.

To learn using wildcard excel characters in more detail, go to this **link**.

**Ex. 4 # Working With Dates in SUMIF Excel Function**

Using the SUMIF function, we can also find the sum of values that occur before or after a particular date. Or does not occur on a particular date.

Let’s see an example to understand the same. In the below example, we have a list of week days along with total hours worked by an employee during those days.

Suppose we want to find sum of total hours worked except on 06-01-2021.

To achieve this, use the following formula:

=SUMIF(A2:A8,"<>"&DATE(2021,1,6),B2:B8)

OR

=SUMIF(A2:A8,"<>"&B7,B2:B8)

In the above example, since we want to exclude a single date, we have used the mathematical operator not equal to (<>).

Also, we have nested the **DATE excel function** inside the SUMIF function to return a date value.

This brings us to the end of this article on SUMIF formula in excel.

Thank You 🙂

## RELATED POSTS

- SUM Function in Excel – Add All Numbers in Cells
- COUNTIF Function In Excel – Counting Cells With Condition
- Excel COUNTIFS Function – Counting Cells with Specified Conditions
- SUMPRODUCT Function in Excel – Usage with Examples
- AND Function in Excel – Check Multiple Conditions
- IF Function in Excel – Check Logical Condition