Excel SUMIFS Function – Multiple Criteria

How to Use the SUMIFS Function in Excel

In this tutorial, you'll learn how to use the Excel SUMIFS function to sum cells that match multiple criteria.

When aggregating in Excel, you may want to calculate the sum of numerical values that satisfy multiple conditions.
The SUMIFS function allows you to find the sum of numbers that satisfy multiple conditions in cell ranges.

In Excel, the SUMIFS function is a function that allows you to specify multiple conditions and find the sum of all data that match more than one condition. For example, in a sales performance chart, you can find the total sales amount by specifying two conditions: "Product" and "Person in charge."

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.

Summary

In this tutorial, you've learned how to use the Excel SUMIFS function to sum the values in a range of cells that meet multiple criteria. 

If you want to specify the condition and get the total value, use the SUMIFS and SUMIF functions.

Mastering various functions such as the SUMIF, SUMPRODUCT, COUNTIF, COUNTIFS, SUBTOTAL, and VLOOKUP functions will help you improve your Excel work efficiency.


See also:
Excel ROUNDUP Function with Examples
Excel MIN Function with Examples
Excel COUNTA Function – Count Non-Blank Cells in Excel
Excel COUNTBLANK Function – Count Blank Cells in Excel
Excel COUNTIFS Function with Examples

Leave a Comment