How to Use SUMIFS to Sum with Multiple Criteria in Excel
Every business spreadsheet eventually asks the same question: of all these numbers, what is the total for just one thing? The total sales for Berlin. The expenses for one project. The invoices from a single month. You could filter the list and read the total off the status bar, but that is a manual step you have to repeat every time the data changes.
SUMIFS answers that question with a single formula that updates itself. You tell it which numbers to add and which conditions those numbers must meet, and it adds only the matching ones. This guide explains how SUMIFS works from the ground up, in plain language and with pictures, so that a first-time user can write a correct formula and understand exactly why it returns the number it does.
FAQ — What does this article answer?
Q: What does the SUMIFS function do?
A: SUMIFS adds up the numbers in a column, but only the rows that meet the conditions you set. For example, it can total the Amount column for every row where Region is "Berlin". You can stack several conditions together, and only rows that satisfy all of them are included.
Q: How is SUMIFS different from SUMIF?
A: SUMIF (no S) handles a single condition and puts the numbers to add last. SUMIFS handles one or more conditions and puts the numbers to add first. The swapped order is the single most common beginner mistake. Because SUMIFS also works perfectly with just one condition, most people simply use SUMIFS for everything.
Q: Can SUMIFS test more than one condition at once?
A: Yes. You can add as many extra range-and-condition pairs as you like, up to 127 of them. A row is only counted when it ticks every box, which is what we call AND logic. We'll go through all the nitty-gritty of that in a minute.
Q: Does SUMIFS do AND or OR?
A: SUMIFS on its own is always AND: all conditions must be true. To get OR behaviour ("Berlin or Munich"), you add two SUMIFS formulas together. This article shows how.
Q: Which Excel versions have SUMIFS?
A: All modern ones. SUMIFS has been built into Excel since the 2007 version, and it works identically in Excel 365, Excel 2021, Excel for the web and Excel for Mac. Unlike newer functions, there is no version compatibility risk.
What SUMIFS does
Imagine a simple sales list. Each row records a Region, a Product and an Amount. You want the total Amount for Berlin. SUMIFS walks down the Region column, checks each row against your condition ("Berlin"), and adds up only the Amount values sitting next to the rows that pass. Everything else is ignored.
That is the whole idea: a conditional total. Rather than summing a whole column, you sum the part of it that matches a rule.
The syntax, one argument at a time
=SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...)Read it as three fixed parts followed by optional extras:
sum_range is the column of numbers you want to add. It always comes first. This is the part that actually gets totalled.
criteria_range1 is the column you want to test, for example, the Region column.
criteria1 is the condition that column must meet, for example "Berlin", or ">100".
After those three, everything comes in pairs. criteria_range2 / criteria2 adds a second test, criteria_range3 / criteria3 adds a third, and so on. Each pair narrows the result further.
There is one rule that beginners must not skip: the sum_range and every criteria_range must be the same size, the same number of rows. If one column runs from row 2 to row 6 and another from row 2 to row 7, Excel cannot line them up and returns a #VALUE! error.
SUMIF vs SUMIFS: the argument-order trap
Excel has two similar functions, and the difference trips up almost everyone at first.
SUMIF (singular) was the original. It takes the column to test first and the numbers to add last:
=SUMIF(range, criteria, [sum_range])SUMIFS (plural) reversed that. It takes the numbers to add first, then the test column and its condition:
=SUMIFS(sum_range, criteria_range1, criteria1, ...)
The practical advice is simple: just use SUMIFS for everything. It does everything SUMIF does, it handles a single condition just as happily as five, and you never have to remember two different argument orders. SUMIF still appears in older workbooks, so it is worth recognising, but there is no reason to write a new SUMIF today.
A first example: one condition
Here is the sales list. The data sits in rows 2 to 6.
| Cell | Region (A) | Product (B) | Amount (C) |
|---|---|---|---|
| Row 2 | Berlin | A | 100 |
| Row 3 | Munich | B | 200 |
| Row 4 | Berlin | B | 150 |
| Row 5 | Berlin | A | 300 |
| Row 6 | Munich | A | 250 |
To total the Amount for Berlin:
=SUMIFS(C2:C6, A2:A6, "Berlin")Excel checks the Region column (A2:A6) against "Berlin", finds three matches (rows 2, 4 and 5), and adds their Amount values: 100 + 150 + 300.
The result is 550. Change any Berlin amount, or add a new Berlin row, and the formula updates on its own. That is the payoff over reading a total off a filtered list.
Adding a second condition: AND logic
Now suppose you want a narrower total: the Amount for Berlin and Product A only. You add a second range-and-condition pair:
=SUMIFS(C2:C6, A2:A6, "Berlin", B2:B6, "A")A row now has to clear two hurdles: its Region must be Berlin and its Product must be A. Only rows 2 and 5 pass both tests, so the result is 100 + 300 = 400.
This is the key mental model: SUMIFS combines its conditions with AND. Every extra pair you add makes the result more specific, never broader.
What about OR?
SUMIFS has no OR mode. If you want the total for Berlin or Munich, you add two SUMIFS together:
=SUMIFS(C2:C6, A2:A6, "Berlin") + SUMIFS(C2:C6, A2:A6, "Munich")Each formula handles one option, and the plus sign combines them. For two or three options this is perfectly readable. If you find yourself adding up many options, that is a signal to move to a PivotTable or Power Pivot instead.
Writing criteria: operators, wildcards and cell references
So far the conditions have been plain text like "Berlin". Criteria can do much more, and this is where SUMIFS becomes genuinely powerful.
A few points that catch beginners out:
Operators go inside the quotes. To sum amounts greater than 100, the whole thing is a text string: ">100". Writing >100 without quotes will not work.
To compare against a cell, join the operator to the cell with &. This is the pattern that unlocks interactive reports. Say cell E1 holds a threshold the user can change. Then:
=SUMIFS(C2:C6, C2:C6, ">"&E1)sums every Amount greater than whatever number is currently in E1. The & glues the ">" operator onto the cell's value to build the condition. Type a new number in E1 and the total recalculates. The same trick lets a dropdown in one cell drive the whole formula, pick "Berlin" from a list and the total follows.
Wildcards work on text. An asterisk * stands for any run of characters, so "B*" matches anything starting with B. A question mark ? stands for exactly one character. Wildcards only apply to text conditions, not numbers.
Summing between two values (a date or number range)
A very common need is "total everything between these two limits", for example, all amounts booked in a single month. The trick is to point two conditions at the same column, one for the lower bound and one for the upper:
=SUMIFS(Amount, Date, ">="&E1, Date, "<="&E2)If E1 holds the first day of the month and E2 the last day, this totals every amount dated within that window. Because both conditions target the Date column and SUMIFS uses AND, only dates that are both on-or-after the start and on-or-before the end survive. The same two-condition pattern works for any numeric range, such as amounts between 100 and 500.
Make it robust with Excel Tables
Hard-coded ranges like C2:C6 have a weakness: when you add a new row of data, the formula does not stretch to include it, and your total silently goes stale. The fix is to put your data in an Excel Table (select the data and press Ctrl+T) and use its column names:
=SUMIFS(Sales[Amount], Sales[Region], "Berlin", Sales[Product], "A")These are called structured references. Sales[Amount] always means the whole Amount column, however many rows the table grows to. New data is included automatically, and the formula reads almost like a sentence. For any SUMIFS you expect to maintain over time, a Table is the safer foundation.
Common mistakes and how to fix them
Keeping the SUMIF argument order. If your SUMIFS returns the wrong number or an error, check the very first argument. In SUMIFS it must be the numbers to add, not the column to test. This is the number one cause of broken SUMIFS formulas.
Mismatched range sizes. #VALUE! almost always means one of your ranges is a different length from the others. Make every range span exactly the same rows. Tables prevent this problem entirely.
Forgetting the quotes or the ampersand. Operators belong inside quotes (">100"), and comparisons against a cell need the & join (">"&E1). Missing either one is a frequent slip.
Numbers stored as text. If a formula returns 0 when you expect a total, the "numbers" may actually be text, often from a pasted export, sometimes with a stray leading space or a hidden non-breaking character. Text that looks like 100 will not match the number 100. Clean the column first; our data-cleaning guide covers exactly this.
Whole-column references on big data. Writing A:A instead of A2:A100000 forces Excel to test every cell in the column and can make large workbooks sluggish. Use a Table, or bound the range to the data you actually have.
Expecting OR from one formula. One SUMIFS is always AND. For OR, add separate SUMIFS formulas together.
When to reach for something else
SUMIFS is the right tool for conditional totals across columns of the same length, combined with AND. Two neighbours are worth knowing about for when you outgrow it.
SUMPRODUCT handles logic that SUMIFS cannot express directly, OR conditions inside a single formula, or conditions based on a calculation rather than a plain column. It is more flexible but harder to read, so reach for it only when SUMIFS genuinely cannot do the job.
Power Pivot and DAX are the answer at scale. When you are joining several tables, working with hundreds of thousands of rows, or rebuilding the same monthly report from fresh exports, a data model with a measure such as SUM(Sales[Amount]) will be faster and far more maintainable than a wall of SUMIFS formulas.
Summary
| Concept | What to remember |
|---|---|
| Purpose | Adds numbers that meet one or more conditions, a conditional total |
| Argument order | sum_range comes first, then range-and-condition pairs |
| vs SUMIF | SUMIF puts sum_range last; just use SUMIFS for everything |
| Multiple conditions | Combined with AND, a row must satisfy them all |
| OR | Add separate SUMIFS formulas together |
| Operators | Go inside quotes: ">100", "<=50", "<>Berlin" |
| Cell as criterion | Join with &: ">"&E1 |
| Range (between) | Two conditions on the same column: ">="&E1 and "<="&E2 |
| Same-length rule | sum_range and every criteria_range must span identical rows |
| Robust ranges | Use an Excel Table with structured references |
SUMIFS rewards a clear mental model. Picture it scanning down your conditions column, keeping only the rows that pass every test, and adding up the numbers next to them. Once that picture is fixed, the operators, wildcards and date ranges are just variations on the same idea.
What to read next
This article pairs well with other SafeOffice guides. If your criteria depend on categories or codes, the SWITCH guide shows how to map them cleanly. If your ranges keep breaking as data grows, the Tables guide is essential. And when SUMIFS reaches its limits on large or multi-table data, the Power Pivot guide is the natural next step.
See: Why every dataset should be an Excel Table
See: How to clean messy data in Excel