Excel SUMIFS Function with Examples


How to Use the SUMIFS Function in Excel

In this tutorial, you'll learn how to use the Excel SUMIFS function.

SUMIFS function sums the values in a range of cells that meet multiple criteria.

SUMIFS() Syntax

The syntax of the SUMIFS() function is as follows:

=SUMIFS(sum_range, range1, criteria1, [range2, criteria2], ...)

Arguments

  • Range1: The 1st range of cells that you want to be evaluated by criteria1
  • Criteria1: The 1st criteria to decide which cells in range1 will be added
  • Range2, criteria2, ... : The additional pairs of range and criteria
  • Sum_range: The range of cells to sum the values

Remarks

  • You can specify the additional pairs of range and criteria up to 127 arguments.
  • Text criteria or criteria, that includes mathematical or logical symbols, need to be enclosed in double quotation marks. If criteria is numeric, double quotation marks are not required.
  • You can use the wildcard characters (*) and (?) in text criteria.
  • SUMIFS() is not case-sensitive. The criteria JOHN and John are the same.

SUMIFS Function Examples

Demo data:

 ABCDEFG
1DateCodeItem NameQuantityPrice ($)Amount ($)Employee
27/20/2019Item0001HP Desktop PC 1312503750Roland
37/20/2019Item0002HP Desktop PC 2210002000James
47/20/2019Item0001HP Desktop PC 1211502300James
57/20/2019Item0003HP Desktop PC 32450900Roland
67/21/2019Item0002HP Desktop PC 229501900Roland
77/22/2019Item0001HP Desktop PC 1111801180Roland
87/23/2019Item0003HP Desktop PC 32440880James

Example 1: SUMIFS with a pair of range and criteria

The following example sums all the amount in column F where the Date is 7/20/2019:

=SUMIFS(F2:F8, A2:A8, DATE(2019,7,20))

As you can see, there is only one pair of range and criteria in the above formula.
The formula returns 8950.

Note that we use the DATE function as the criteria in this example.

You can also use the text criteria, as the following example shows:

=SUMIFS(F2:F8, A2:A8,"7/20/2019")

Example 2: SUMIFS() with 2 pairs of ranges and criteria

You can have 2 pairs of ranges and criteria in the SUMIFS function, as the following example shows:

=SUMIFS(F2:F8, A2:A8,">=7/21/2019",G2:G8, "James")

SUMIFS sums all the amounts in the range F2: F8 that meet the following conditions:

  • The date is greater than 7/21/2019 in the range A2: A8
  • The employee must be James in the range G2: G8.

The above formula returns 880.

Example 3: SUMIFS between 2 dates

The following example shows how to sum values between two dates:

=SUMIFS(F2:F8,A2:A8,">=7/21/2019", A2:A8,"<=7/23/2019")

As you can see, the 1st and 2nd criteria range (A2: A8) are the same, because we want to sum all values in the range F2: F8 with two conditions on the same column A, where ">=7/21/2019" is the 1st criteria and "<=7/23/2019" is the 2nd criteria. 

SUMIFS() sums all the value between 7/21/2019 and 7/23/2019 and returns 3960.

Example 4: SUMIFS with 3 conditions

Here is an example of SUMIFS() with three conditions:

=SUMIFS(F2:F8,A2:A8,">=7/21/2019", A2:A8,"<=7/23/2019", G2:G8, "James")

SUMIFS() sums all values in the range F2: F8, where the date is between 7/21/2019 and 7/23/2019, and the employee is James.

The above formula returns 880.

Example 5: SUMIFS is not case-sensitive

The SUMIFS function is not case-sensitive.
Here is an example:

=SUMIFS(F2:F8,A2:A8,">=7/21/2019", A2:A8,"<=7/23/2019", G2:G8, "james")

The above formula returns the same result as the previous formula.

Example 6: Partial matching in criteria

You can perform partial matching in the SUMIFS function, as the following example shows:

=SUMIFS(F2:F8,A2:A8,">=7/21/2019", A2:A8,"<=7/23/2019", G2:G8, "J*m??")

In the formula, the 3rd criteria, J*m?? matches any text that begins with J, followed by any sequence of characters and then followed by m, and ends with any 2 characters

The criteria J*m?? matches for James.

Note that the wildcard character asterisk (*) matches any sequence of characters and question mark (?) matches any single character.

In this tutorial, you've learned how to use the Excel SUMIFS function.

SUMIFS is a function to sum the values in a range of cells that meet multiple criteria. 

If you want to sum the values in a range of cells that meet a single criteria, use the SUMIF function.

Leave a Reply

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