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."
The syntax of the SUMIFS() function is as follows:
=SUMIFS(sum_range, range1, criteria1, [range2, criteria2], ...)
- 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
- 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
|1||Date||Code||Item Name||Quantity||Price ($)||Amount ($)||Employee|
|2||7/20/2019||Item0001||HP Desktop PC 1||3||1250||3750||Roland|
|3||7/20/2019||Item0002||HP Desktop PC 2||2||1000||2000||James|
|4||7/20/2019||Item0001||HP Desktop PC 1||2||1150||2300||James|
|5||7/20/2019||Item0003||HP Desktop PC 3||2||450||900||Roland|
|6||7/21/2019||Item0002||HP Desktop PC 2||2||950||1900||Roland|
|7||7/22/2019||Item0001||HP Desktop PC 1||1||1180||1180||Roland|
|8||7/23/2019||Item0003||HP Desktop PC 3||2||440||880||James|
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:
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:
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 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.