SUMIF Function in Excel – Total Based On Condition

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.

Sum only values greater than a number

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.

Result - SUM Values 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.

SUMIF Function Using Cell Reference

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:

Logical Mathematical Operators Excel

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.

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

Region Wise Sales - SUMIF Function Usage

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)
SUMIF Function Using Text Criteria

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

Infographic - SUMIF Formula Function in Excel

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.

Sample Data - Using SUMIF Function with Partial Match

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)
Using Asterisk in Excel - SUMIF Function

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.

Hours Worked by Employee During Week

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)
Using Dates with SUMIF Function - Example

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 πŸ™‚

Leave a Comment