Function guides ยท Math
SUMIFS function
Adds the values in a range that meet every one of several conditions.
Syntax
=SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...)
| Argument | What it is |
|---|---|
sum_range | The cells to add. |
criteria_range1 | The first range to test. |
criteria1 | The condition for that range. |
... | Up to 127 range and condition pairs. |
What SUMIFS does
SUMIFS adds the values in one range, but only for rows where every one of several conditions holds. It is the multi-condition version of SUMIF and the function behind most summary tables.
The argument order is the thing to learn. The range of numbers to add comes first, then any number of pairs: a range to test and the condition to test it with. Excel goes through the rows in step and includes a row only if every pair agrees. The test ranges and the sum range must have the same number of rows.
If a total ever comes out as zero when you expect a number, the criteria are almost always the problem.
SUMIFS examples
1. Sales by region and product
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Region | Product | Sale | ||
| 2 | West | Kit | 450 | 725 | |
| 3 | East | Kit | 900 | ||
| 4 | West | Pro | 620 | ||
| 5 | North | Kit | 310 | ||
| 6 | West | Kit | 275 |
The first argument is what to add. Every pair after that narrows the rows. Only the West rows for "Kit" count: 450 and 275.
2. Sum inside a date range
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Date | Amount | ||
| 2 | 2/27/2026 | 200 | 490 | |
| 3 | 3/4/2026 | 340 | ||
| 4 | 3/19/2026 | 150 | ||
| 5 | 4/2/2026 | 90 |
The same date column is used twice, once for the start and once for the end. March 2026 includes the 4th and the 19th. The February and April rows stay out.
3. A summary table with cell criteria
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Region | Product | Sale | Region | Kit | |
| 2 | West | Kit | 450 | West | 725 | |
| 3 | East | Kit | 900 | |||
| 4 | West | Pro | 620 | |||
| 5 | West | Kit | 275 |
One formula with mixed references serves every cell in a region-by-product table. The region comes from column E and the product from row 1. Fill it across and down.
Common SUMIFS mistakes
- Putting sum_range last. That is SUMIF's order. In SUMIFS the numbers to add come first. If you reverse it you get 0 or an error.
- Ranges of different sizes. Every range must be the same height. A mismatch returns #VALUE!.
- Expecting OR. Every condition must hold. For West or East, add two SUMIFS, or wrap an array of criteria in SUM.
- Hard-typing criteria that should be cells. A typed region means one formula per row. Use the cell reference and absolute ranges so the formula fills.
SUMIFS on Excel job tests
Summary tables are the usual setting: total revenue for each region and product, or the expenses for each department in Q1. Every phrase that narrows the rows is a pair of range and criterion.
Build the formula in the top-left cell of the table with mixed references, such as $E2 for the region down the side and F$1 for the product across the top, then fill right and down. If the bottom-right cell is wrong, the dollar signs are in the wrong place. Check one cell against a filtered total.
For date windows, decide whether the end date is included, and say so with <= or <.
Related functions
SUMIFS questions
- What is the difference between SUMIF and SUMIFS?
- SUMIF takes one condition with the sum range last. SUMIFS takes many with the sum range first.
- Can SUMIFS do OR?
- No, conditions combine with AND. Add several SUMIFS or use SUM with an array constant:
=SUM(SUMIFS(C:C,A:A,{"West","East"})). - How many conditions can I use?
- Up to 127 range and criteria pairs.
- How do I sum between two dates?
- Use the date column twice: one criterion with >= the start date and one with <= the end date, or < the day after.
Practice SUMIFS
The lesson SUMIFS: total with several conditions (module 8 of Everyday Excel) has checked SUMIFS exercises. It is part of Full Access; only the first two lessons of each module are free. The free 5-item sample test uses the same kind of formulas in a working grid if you want to try before you decide.
Try a 5-item Excel test sample