Excel SUMIF Function and Formula with Examples


How to Use the Excel SUMIF Function

In this tutorial, you'll learn how to use the Excel SUMIF function. In Excel, a SUMIF is a function to sum 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 cells to add.
  • Sum_range: [Optional]. The actual cells in a range that you want to sum.

Remarks

  • If sum_range omitted, a SUMIF 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 specified criteria are numeric, double quotation marks are not required.
  • You can use the wildcard characters* and in criteria.
  • SUMIF is not case-sensitive. For example, JOHN and John are the same.

SUMIF Function Examples

Suppose we have the demo data, as shown below.

 ABCDEFG
1DateCodeItem NameQuantityPrice ($)Amount ($)Employee
210/20/2019Item1001Sony Desktop PC 1210502100Roland
310/20/2019Item1002Sony Desktop PC 229501900James
410/20/2019Item1001 Sony Desktop PC 1112501250James
510/20/2019Item1003Sony Desktop PC 32450900Roland
610/21/2019Item1002Sony Desktop PC 2310003000Roland
710/22/2019Item1001Sony Desktop PC 1111901190Roland
810/23/2019Item1003Sony Desktop PC 32450900James

SUMIF and DATE Function

Let's say you want to sum all of the values in the range F2: F8, where the date is 10/20/2019. 

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 the criteria, and F2: F8 is the range of cells to sum.

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

The formula returns 6150.

Note that here the DATE function is used as criteria.

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

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

The formula returns the same value (6150).

SUMIF with a Reference Criteria

The following example shows how to use a reference in the criteria:

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

H2 is a cell reference to criteria.

SUMIF with Logical Operators in Criteria

You can also use logical operators, ">=" , "<=", "<" or ">" in text criteria.

Let's say you want to sum only the values of cells that are greater than 2000 in the range of cells.

The SUMIF formula is as follows:

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

Here, as you can see, there are only two arguments and the third (sum_range) argument omitted. The range F2: F8 becomes both range and sum_range. Based on the above condition, SUMIF returns 5100.

You can also use a logical operator, as the following example shows:

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

As you can see, criteria is a text 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.

You can use a logical operation greater than or equal to (>=), as the following example shows:

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

Similarly, you can also use a logical operator less than or equal to (<=) in criteria, as the following example shows:

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

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

Partial Matching Using Asterisk (*)

The following illustrates an example of using (*) in criteria:

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

The criteria So* matches any text string that begins with So, and everything is ok for the rest. 

SUMIF sums all the values of cells that the item name starts with So

The formula returns 9990.

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

Partial Matching Using Question Mark (?)

You can use the wildcard (?) in criteria, as the following example shows:

=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 returns 0 because no match meets the specified condition.

SUMIF is not case-sensitive

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

Here is an example:

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

You can also replace the criteria James with JAMESJames, or James, because SUMIF treats the lowercase and uppercase characters the same.

If you need to sum all of the values in a specified 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 a function to sum all of the values in a range of cells that meet single criteria. To specify multiple criteria, use SUMIFS.

Leave a Reply

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