​​​​Sumifs

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
1RangeThe cell range of which the sum is calculated.
2Condition 1 rangeThe range on which Condition 1 is applied. If the condition is met for a value in this range, the corresponding value in Range is summed.
3Condition 1The condition that’s applied to values inCondition range 1 . If the condition is met, corresponding values in Range are summed.
4Condition 2 range (Optional)The range on which Condition 2 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 Range is summed.
5Condition 2 (Optional)The condition that’s applied to values inCondition range 2 . 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 Range 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")
A basic SUMIFS example. Takes a cell range with values 1 through 5 and sums them if the other ranges contain A and Yes respectively. In this case, the Sumifs function returns 3.
A basic SUMIFS example. Takes a cell range with values 1 through 5 and sums them if the other ranges contain A and Yes respectively. In this case, the Sumifs function returns 3.

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:

OperatorMeaning
=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:

Summing the values in the Sales volume column when age is greater than 30 and Status is not Retired.

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

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:

  1. 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.
__CONFIG_colors_palette__{"active_palette":0,"config":{"colors":{"36c04":{"name":"Main Accent","parent":-1},"3a8fd":{"name":"Accent Light","parent":"36c04","lock":{"saturation":1,"lightness":1}}},"gradients":[]},"palettes":[{"name":"Default","value":{"colors":{"36c04":{"val":"rgb(255, 204, 102)","hsl":{"h":40,"s":1,"l":0.7}},"3a8fd":{"val":"rgb(241, 241, 241)","hsl_parent_dependency":{"h":0,"s":0,"l":0.94}}},"gradients":[]},"original":{"colors":{"36c04":{"val":"rgb(255, 204, 102)","hsl":{"h":40,"s":1,"l":0.7}},"3a8fd":{"val":"rgb(241, 241, 241)","hsl_parent_dependency":{"h":0,"s":0,"l":0.94}}},"gradients":[]}}]}__CONFIG_colors_palette__

​Get Good at Excel on Autopilot

Sign up to our newsletter and receive ​Excel articles, tips and tricks delivered straight to your inbox. All you have to do is read them! ​Plus, to get you started, you'll receive a PDF with 200+ Excel shortcuts.

__CONFIG_colors_palette__{"active_palette":0,"config":{"colors":{"2dd0d":{"name":"Main Accent","parent":-1}},"gradients":[]},"palettes":[{"name":"Default Palette","value":{"colors":{"2dd0d":{"val":"rgb(45, 164, 92)","hsl":{"h":143,"s":0.57,"l":0.41}}},"gradients":[]},"original":{"colors":{"2dd0d":{"val":"rgb(19, 114, 211)","hsl":{"h":210,"s":0.83,"l":0.45}}},"gradients":[]}}]}__CONFIG_colors_palette__
Join List
>