SUMIFS
Sums a range, but only for rows where every listed condition holds.
SUMIFS shows up constantly in finance work: summing revenue for a specific region and quarter, totaling expenses for one cost center within a date range, adding up trade P&L for a given desk and month. The formula itself isn't hard; what trips people up is a structural rule that Excel's own documentation buries instead of leading with.
SUMIFS adds up a range of numbers, but only the ones where every condition you give it is true at the same time. One condition alone is SUMIF (singular); two or more conditions is SUMIFS (plural), and the argument order flips between them, which is the first thing that trips people up switching from one to the other. Note the order: the range you're summing comes first, then pairs of (range to check, condition to match). That's the opposite order from SUMIF, where the sum range comes last.
Syntax
| Argument | Description |
|---|---|
| sum_range | Required. The range to total up. |
| criteria_range1, criteria1 | Required. A range to check and the condition it must meet. |
| criteria_range2, criteria2, ... | Optional. Additional range/condition pairs: every pair must be true for a row to count. |
The mistake almost everyone makes
Every criteria_range and the sum_range must be exactly the same size and shape: same number of rows, same starting and ending point. If your sum range is D2:D500 but one of your criteria ranges is B2:B499 (off by one row, often from a copy-paste or a filtered range), Excel won't throw an error. It will just silently produce a wrong number, because it stops matching a row early. This is the single most common source of “my SUMIFS total doesn't match the actual data” bugs, and because there's no error message, it can sit wrong in a model for weeks before anyone notices.
The fix is a habit, not a formula trick: always select all your ranges the same way: either all full columns (D:D, B:B, C:C) or all the exact same row range, never mixed.
A real example: desk P&L by month and instrument type
Say you're summarizing trading P&L and need the total for the Rates desk, for March, for swaps only:
Every one of PnL_Range, Desk_Range, Month_Range, and Instrument_Range needs to cover the identical set of rows. This is exactly the kind of three-condition summary that comes up in daily and monthly P&L reporting, and it's a common technical-screen question because it tests two things at once: do you know SUMIFS syntax, and do you actually check that your ranges line up before trusting the output.
Worked examples
| A | B | C | |
|---|---|---|---|
| 1 | Region | Qtr | Sales |
| 2 | West | Q1 | 120 |
| 3 | West | Q2 | 90 |
| 4 | East | Q1 | 60 |
Common mistakes
- Every criteria_range and the sum_range must be exactly the same size and shape: a one-row mismatch (often from a copy-paste or a filtered range) silently drops rows with no error at all.
- Mixing up the argument order with SUMIF, where the sum range comes LAST instead of first.
- Using text comparison operators like ">50" as a bare string on a numeric column without quotes, or the reverse.
Related functions
Try it yourself
A free, graded practice exercise using this exact function: instant feedback, no signup required.
Practice SUMIFS →