SUMIF

What it does

SUMIF checks one range against a condition and adds the matching values from another range. Use it for totals per category, person or region, or for the values above or below a limit.

Syntax

=SUMIF(range, criteria, [sum_range])

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

Arguments

range

The cells to check against the condition, for example the category column.

criteria

The condition. Text goes in quotes ("Food"), and so do comparisons (">100"). It can also be a cell that holds the value to match.

sum_range

The cells to add. Leave it out to add the checked cells themselves, which suits conditions like ">100" on a column of amounts.

Example

Expenses are listed by category. How much went on food?

The formula in D2:

=SUMIF(A2:A6,"Food",B2:B6)

Result: 41

Rows 2, 4 and 6 are Food, so their amounts 18, 9 and 14 are added: 41. Train and hotel are skipped. Upper and lower case don't matter, so food and FOOD both match.

Common mistakes

• Making the range and the sum range different sizes. Excel lines them up from the first cell, so a short sum range quietly adds the wrong cells.
• Writing a comparison without quotes. The condition is text: ">100", or ">"&D1 to compare with a cell.
• Putting the sum range first. That's the order in SUMIFS, not in SUMIF.

In other Excel languages

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

Practise it

• SUMIF: add up what matchesModule 2 · Adding up

More from this module

• SUM
• SUMIFS
• SUMPRODUCT

All functions