The Excel SUMIF function sums the values in a given cell range if they meet the specified condition.

## Syntax

**=SUMIF(**`Range`

**,** `Condition`

**, [**`Sum range`

**]) **

Argument | Argument description | |
---|---|---|

1 | Range | The cell range that the condition is applied to. If you don’t use the `Sum range` argument, `Range` is summed instead. |

2 | Condition | The condition that determines which values are summed. |

3 | Sum range (Optional) | The cells that will be summed up. If you don’t use the `Sum range` argument, `Range` is summed instead. |

## Example 1: Simple Excel Sumif formula

A barebones example of a Sumif formula can look like this:

`=SUMIF(A1:A3, " >1")`

### Explanation

The result is 5 because the formula checks for each value in cells **A1**, **A2 **and **A3** if they meet the condition given. The condition is “> 1”, so values have to be larger than 1.

Let’s walk through the values: 1 is **not** **larger **than 1. 2 **is larger **than 1. And 3 **is also larger** than 1. So 2 and 3 meet the condition and are added together. 2 + 3 = 5 and therefore the resulting value is 5.

## Example 2: Sum range

In the above example, the `Range`

argument is used as both the range that is summed *and *the range that the condition is applied to.

We can separate these two functionalities using the

argument. When this argument is used, the cells in `Sum range`

`Range`

will only be used to check if they meet the condition. The cells in

will only be used to sum. Here’s a quick example:`Sum range`

### Explanation

The returned value is 40 because we are looking in Range **B2**:**B4** for cells that contain the text *Yes*. There are two cells that meet this condition **B2** and **B4**, the first and third cell in the range.

Then, we sum the values in `Sum range`

**C2**:**C4 **if their corresponding value in `Range`

meets the condition. We know that the first and third cell met the condition, so the first and third cell of the

are summed: 10 + 30 = 40. And 40 is returned.`Sum range`

## Example 3: Wildcards and Sumif

The Sumif function allows for all sorts of conditions as `Condition`

argument. They can be texts, numbers and dates. They can be combined with conditional operators or functions that return TRUE or FALSE. For more information on different types of conditions, check out this section of our complete guide on the If function.

Another thing that’s allowed is the use of wildcards in the `Condition`

argument.

Wildcards are characters like ? and * that allow you to search for an approximate match instead of an exact word match.

**Note:** for more information on wildcards, check this section about wildcards from our Find and Replace post.

Here’s an example formula where we use the asterisk (*) wildcard to match with a last name regardless of first name.

### Explanation

123 is returned because that is the sum of the ages of the members of the Collins family (50 + 51 + 22 = 123).

The condition **Collins* matches with any names ending in *Collins*, regardless of what text is before that. Therefore, we match with every Collins family member and we use the

argument to sum their ages.`Sum range`

## Questions

**Can I use multiple conditions with a Sumif function?**

Yes, you can. The easiest way to do this is by using the Sumifs function, which allows for multiple (range, condition) pairs.

An alternative with one range and multiple conditions is to use the regular Sumif function, combined with logical operators (And, Or, Not, Xor).

**Can an array be used instead of a cell range?**

No, the Sumif and Sumifs functions are not compatible with arrays as input argument. You can use the Sumproduct function as a workaround.

**Is Sumif case sensitive?**

No, Sumif is **not **case sensitive. So if your condition is “*POTTER*” you will also find Potter, pOTTER and potter.

## Drawbacks

The Excel Sumif function has some drawbacks:

- We’ve mentioned it before: you cannot use arrays instead of cell ranges with the SUMIF or SUMIFS functions.