SOMME.SI.ENS(SUMIFS)

What it does

SUMIFS adds a value only when every condition is true for its row. Each condition is a pair: a range to check and what it must match. Unlike SUMIF, the range to add comes first.

Syntax

=SOMME.SI.ENS(sum_range; criteria_range1; criteria1; [criteria_range2; criteria2]; …)

Arguments in square brackets are optional, and … means you can add more of the same. In this Excel language, arguments are separated by ;

Arguments

sum_range

The cells to add.

criteria_range

A range to check, the same size as the sum range.

criteria

What that range must match: text in quotes, a comparison such as ">=10", or a cell.

Example

How much of the spending was food in January?

The formula in E2:

=SOMME.SI.ENS(C2:C6;A2:A6;"Food";B2:B6;"Jan")

Result: 27

Only rows 2 and 5 are both Food and Jan, so 18 + 9 = 27. Row 3 is food in February and row 4 is January but train, so neither counts.

Common mistakes

• Using the SUMIF order and putting the sum range last. In SUMIFS it always comes first.
• Expecting either/or logic. Every pair must match; to add food or train, add two SUMIFS results together.
• Ranges of different sizes. Unlike SUMIF, this gives a #VALUE! error.

In other Excel languages

Excel translates function names, and in some languages the argument separator too. Type the name your Excel shows.

Practise it

• SUMIFS: several conditionsModule 2 · Adding up
• Checking invoicesModule 14 · Combined skills

More from this module

• SOMME
• SOMME.SI
• SOMMEPROD

All functions