Excel Sumif Function - Syntax and examples

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")

Simple Excel SUMIF example. Given a cell range with values 1, 2 and 3, and the condition > 1, the Sumif function returns 5. — Simple SUMIF example. Given a cell range with values 1, 2 and 3, and the condition > 1, the Sumif function returns 5.

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 Sum range argument. When this argument is used, the cells in Range will only be used to check if they meet the condition. The cells in Sum range will only be used to sum. Here’s a quick example:

Summing the values in Sum range if the corresponding value in Range is — Summing the values in `Sum range` if the corresponding value in `Range` is “Yes”.

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 Sum range are summed: 10 + 30 = 40. And 40 is returned.

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 Conditionargument.

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.

Using Excel Sumif with an asterisk (*) wildcard to sum the ages of the Collins family. — Using Sumif with an asterisk (*) wildcard to sum the ages of the Collins family.

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 Sum range argument to sum their ages.

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.