The Excel SUMIFS function sums the values in a given cell range if they meet multiple criteria. So for example sum all grades of people that live in a certain country (1st condition) and whose names start with an A (2nd condition).
Syntax
=SUMIFS(Range, Condition range 1, Condition 1, [Condition range 2, Condition 2], … )
| Argument | Argument description | |
|---|---|---|
| 1 | Range | The cell range of which the sum is calculated. |
| 2 | Condition 1 range | The range on which |
| 3 | Condition 1 | The condition that’s applied to values in |
| 4 | Condition 2 range (Optional) | The range on which |
| 5 | Condition 2 (Optional) | The condition that’s applied to values in |
| 6, 7, … | Condition range 3, Condition 3, … (Optional) | Up to 127 pairs of condition ranges and conditions. |
Example 1: Simple Excel Sumifs formula
A simple way of using the Sumifs formula could look something like this:
=SUMIFS(B2:B6,C2:C6,"A",D2:D6,"Yes")
Explanation
The result is 3 because the formula checks for each value in cells C2:C6 if it contains the value A and for the values in cell range D2:D6 if they contain Yes. It does so on a cell-by-cell basis. Beginning with the first cells of the ranges to see if they meet their condition: C2 and D2. C2 contains A and D2 contains Yes, so the first cell of the summing range (B2) is added to the sum.
In the same way, C3 and D3 contain A and Yes, so B3 is summed as well. Then, C4 does contain A, but D4 contains No. So not all conditions are met and B4 is not added to the sum. Similarly, B5 and B6 are not added because not all conditions are met. The result ends up being B2 + B3 = 1 + 2, which is 3.
Example 2: More condition types
The conditions we used in the above example are as simple as they get. By only using the text A or Yes as the condition argument, we check if the cell content exactly matches the value. But there are more types of conditions we can use.
An often-used way of creating conditions is by combining any sort of data (numbers, text, dates, etc) with a comparison operator. Here’s an overview of the operators you can use to form conditions:
| Operator | Meaning |
|---|---|
| = | Equal to |
| <> | NOT equal to |
| > | Greater than |
| < | Less than |
| >= | Greater than or equal to |
| <= | Less than or equal to |
Let’s use these comparison operators in an example:
Explanation
Sumifs returned the value 6000. That is because we are looking in cell range C2:C4 for cells that contain values larger than 30. While at the same time, we’re also looking in cell range D2:D6 for values not equal to Retired. If both of these conditions are fulfilled, we add the corresponding value in the Sales volume column (B2:B6) to the sum.
There are two cells that meet both of the conditions: B3 and B5. Summing their values (2000 + 4000) gives us 6000. So 6000 is returned.
Note: Using wildcards with Sumifs
You can use many sorts of conditions as Condition arguments. Conditions can be texts, numbers, and dates. And they can be combined with conditional operators.
Another useful feature that you can use in the Conditionargument are wildcards: characters like ? and * that allow your textual conditions to match with an approximate match instead of an exact word match.
Note: if you’d like to know more about wildcards, you can check out this section about wildcards from our Find and Replace post.
Questions
No, the Sumifs function (and Sumif as well) is not compatible with arrays as input arguments. But you can use the Sumproduct function as a workaround.
No, Sumifs is not case sensitive. So a condition that checks for MARCEL will also find Marcel, mARCEL and marcel.
Drawbacks
The Excel Sumifs function has some drawbacks:
- We’ve mentioned it before: you cannot use arrays instead of cell ranges with the SUMIFS function. It cannot be used with any of the IF(S) family of functions: SUMIF(S), COUNTIF(S), AVERAGEIF(S) all cannot use arrays. However, the SUMPRODUCT function can be used with arrays. Often, you can restructure your formula to use Sumproduct instead of one of the other functions of the IF(S) family of Excel functions.