Excel SUMIF Function and Formula with Examples


How to Use the SUMIF Function and Formula in Excel

In this tutorial, you'll learn how to use the SUMIF function and formula in Excel. SUMIF function in Excel is a function to sums the values in a range of cells that meet single criteria.

SUMIF Syntax

The syntax of the SUMIF function is as follows:

=SUMIF(range, criteria, [sum_range])

Arguments

  • Range: The range of cells that you want to be evaluated by the criteria you specify.
  • Criteria: The criteria used to determine which cells to add.
  • Sum_range: [Optional]The actual cells in a range that you want to sum.

Remarks

  • If sum_range omitted, the Excel SUMIF function adds the cells specified in the range argument.
  • Text criteria or criteria, which include mathematical or logical symbols, need to be enclosed in double quotation marks. If the specified criteria are numeric, double quotation marks are not required.
  • You can use the wildcard characters* and in criteria.
  • SUMIF is not case-sensitive. So, for example, JOHN and John are treated the same.

SUMIF Function Examples

The following table is a sample list of sales records we're going to use in our examples.

 ABCDEFG
1DateCodeItem NameQuantityPrice ($)Amount ($)Employee
210/20/2019Item1001Sony Desktop PC 1210502100Roland
310/20/2019Item1002Sony Desktop PC 229501900James
410/20/2019Item1001 Sony Desktop PC 111250 1250James
510/20/2019Item1003Sony Desktop PC 32450 900 Roland
610/21/2019Item1002Sony Desktop PC 231000 3000 Roland
710/22/2019Item1001Sony Desktop PC 1111901190Roland
810/23/2019Item1003Sony Desktop PC 32450 900James

SUMIF with DATE Function

Let's say you want to sum all the values in the range F2:F8 where the date is 10/20/2019, then you can write the formula as follows:

=SUMIF(A2:A8, DATE(2019,10,20), F2:F8)

Here, A2:A8 is the criteria range, DATE(2016,10,5) is criteria, and F2:F8 is the range of cells you want to sum.

SUMIF sums all the values in the range F2F8, where the value of the cells in the Date column is equal to October 20th, 2019

The above formula returns 6150.

Note that here we use the DATE function as criteria.

If you use the text criteria, the SUMIF formula will look like this:

=SUMIF(A2:A8, "10/20/2019", F2:F8)

This formula returns the same result (6150) as the previous formula.

SUMIF with a Reference Criteria

In this example, you'll learn how to use the reference criteria in the SUMIF function.

The formula using a reference in criteria would look like this:

=SUMIF(A2:A8, H2, F2:F8)

H2 is a cell reference to criteria.

SUMIF with Logical Operators in Criteria

In this example, I show you how to use logical operators, ">=" , "<=", "<" or ">" in a text criteria.

In this example, let's say you want to sum only the values of cells that are greater than 2000 in the range of cells.

You can write the SUMIF formula as follows:

=SUMIF(F2:F8, ">2000")

Here, as you can see, there are only two arguments and the third (sum_range) argument omitted. So, the range F2: F8 becomes both range and sum_range. Based on the specified condition above, SUMIF sums all the values that are greater than 2000 in the range F2F8, and returns 5100.

Let's look at another example:

=SUMIF(A2:A8,">10/22/2019", F2:F8)

As you can see, the second argument is a text criterion that includes the logical operator (>). SUMIF sums all the values of cells that are greater than 10/22/2019 in column A and returns 900.

Next, let's look at an example that uses a logical operator (>=):

=SUMIF(A2:A8,">=10/22/2019", F2:F8)

SUMIF sums all the values of cells that are greater than or equal to 10/22/2019 in column A and returns 2090.

Similarly, to use a logical operator "<=" in criteria, you can write the SUMIF formula as follows:

=SUMIF(A2:A8,"<=10/20/2019",F2:F8)

SUMIF sums all the values of cells that are less than or equal to 10/202019 in column A and returns 6150.

You also can perform a partial matching in the SUMIF function. To perform a partial matching, you need to use the wildcard characters (*) and (?) in text criteria.

Partial Matching Using Asterisk (*)

In this example, we use (*) in a text criterion.
Let's take a look at a formula below:

=SUMIF(C2:C8,"So*", F2:F8)

The criteria So* matches any text string that begins with So, and for the rest, everything would be fine. So, SUMIF sums all the values of cells that the item name starts with So.   

The result of the above formula is 9990.

Note that, the wildcard characters (*) matches any sequence of characters.

Partial Matching Using Question Mark (?)

In this example, we use the wildcard (?) in a text criterion.

Let's take a look at the formula below.

=SUMIF(C2:C8, "???? Desktop PC", F2:F8)

Note that a wildcard (?) is different from (*). It matches any single character.

The criteria ???? Desktop PC matches any text string that begins with any four lengths text string and ends with Desktop PC.

SUMIF sums all the values of cells that the item name matches the specified condition above and returns 0. 

SUMIF is not Case-Sensitive

SUMIF is not case-sensitive, so James, james, and JAMES  text criteria are treated the same.

Let's take a look at the formula below.

=SUMIF(G2:G8,"James",F2:F8)

In the above formula, you can replace the criteria James with JAMESjames or JaMeS, because SUMIF treats the lowercase and uppercase characters the same.

If you want to sum all the values in a range of cells that meet more than one criteria, use SUMIFS.

In this tutorial, you've learned how to use the Excel SUMIF functionSUMIF is used to sum all the values in a range of cells that meet single criteria. If you want to specify multiple criteria, use the SUMIFS function.

Leave a Reply

Your email address will not be published. Required fields are marked *