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 is applied. If the condition is met for a value in this range, the corresponding value in is summed. |

3 | Condition 1 | The condition that’s applied to values in . If the condition is met, corresponding values in are summed. |

4 | Condition 2 range (Optional) | The range on which is applied. If the condition is met for a value in this range and the condition(s) in the other condition range(s) are met as well, the corresponding value in is summed. |

5 | Condition 2 (Optional) | The condition that’s applied to values in . If the condition is met for a value in this range and the condition(s) in the other condition range(s) are met as well, the corresponding value in is summed. |

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 `Condition`

argument 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

**Can an array/multiple arrays be used instead of cell ranges?**

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.

**Is Sumifs case sensitive?**

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.