SUMIFS in Excel
Last updated
SUMIFS adds up the numbers in a range when several conditions are all true on the same row. For example, =SUMIFS(D2:D13,A2:A13,"North",B2:B13,"Olivia Reed") adds the amounts in D2:D13 where the region is North and the salesperson is Olivia Reed. The range to add always comes first.
SUMIFS syntax
=SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], …)
- sum_range
- The cells to add, e.g.
D2:D13. Unlike SUMIF, it comes first and it is required. - criteria_range1
- The first range to test, such as a column of regions. It must have the same number of rows as sum_range.
- criteria1
- The condition for criteria_range1: a value (
"North"), a comparison (">=500"), a cell reference (F2) or a wildcard pattern ("Laptop*"). Text and comparisons go in double quotes. - criteria_range2, criteria2, …
- Optional. More range and condition pairs, up to 127. A row is added only when it meets every condition.
Practice SUMIFS here
Type your formula straight into the sheet. It’s checked like real Excel, and you can write it with commas or semicolons.
Total for one salesperson in one region
In F2, write a SUMIFS that adds the amounts in column D for Olivia Reed in the North region, using the orders in A1:D13.
| Region | Salesperson | Product | Amount ($) | Olivia Reed, North ($) | |
| West | Emma Hughes | Monitor 27" | 640.00 | ||
| South | James Carter | Monitor 24" | 420.00 | ||
| East | Sofia Patel | Laptop 15" | 1,480.00 | ||
| North | Liam Brooks | Keyboard | 180.00 | ||
| North | Olivia Reed | Laptop 14" | 980.00 | ||
| South | Noah Bennett | Laptop 14" | 1,000.00 | ||
| North | Olivia Reed | Monitor 27" | 1,150.00 | ||
| East | Ava Collins | Headset | 240.00 | ||
| North | James Carter | Laptop 15" | 1,520.00 | ||
| South | Sofia Patel | Keyboard | 95.00 | ||
| West | Olivia Reed | Laptop 14" | 870.00 | ||
| North | Emma Hughes | Keyboard | 500.00 |
How to use SUMIFS, step by step
- Click the cell where you want the total and type
=SUMIFS(. - Select the column of amounts to add first, then type a comma.
- Select the first column to test, type a comma, then type its condition in double quotes or click a cell that holds it.
- Add more range and condition pairs the same way, each with the same rows as the amounts, close the bracket and press Enter.
SUMIFS examples
Two text conditions
=SUMIFS(D2:D13,A2:A13,"North",B2:B13,"Olivia Reed")
Adds the amounts on rows where the region is North and the salesperson is Olivia Reed. Both conditions must match on the same row.
Amounts between two values
=SUMIFS(D2:D13,D2:D13,">=500",D2:D13,"<1000")
Tests the same column twice to add amounts from 500 up to, but not including, 1,000. Choose ">=" or ">" to decide whether each bound counts.
Conditions taken from cells
=SUMIFS($D$2:$D$13,$A$2:$A$13,F2,$C$2:$C$13,G2)
Type the region in F2 and the product in G2 and the total updates. With the ranges locked by $, you can copy the formula down a summary table.
Wildcard and comparison together
=SUMIFS(D2:D13,C2:C13,"Laptop*",D2:D13,">=1000")
Adds every product that starts with Laptop, but only orders of 1,000 or more. The asterisk stands for any characters.
Common SUMIFS problems and how to fix them
| Result | Cause | Fix |
|---|---|---|
| 0 instead of a total | No row meets every condition at once: a typo, an extra space, a number stored as text, or two conditions that can’t both be true, like ">1000" and "<500". | Test each condition on its own with SUMIF to find the one that matches nothing, then clean the data with TRIM. |
| #VALUE! | sum_range and a criteria range don’t have the same size, e.g. D2:D13 and A2:A12. | Select every range from the same first row to the same last row. |
| Wrong total, no error | The formula was written in SUMIF order, with the range to add last, so SUMIFS adds the wrong column or tests the amounts. | Put the range to add first: =SUMIFS(D2:D13,A2:A13,"North"). |
| Wrong totals after copying | The ranges moved down one row each time the formula was copied. | Lock every range with $ (F4) and leave only the criteria cells relative. |
SUMIFS vs SUMIF
SUMIF takes one condition and puts the range to add last: =SUMIF(A2:A13,"North",D2:D13). SUMIFS takes one or more conditions and puts the range to add first: =SUMIFS(D2:D13,A2:A13,"North"). Both give the same result with a single condition, so you can use SUMIFS everywhere and keep one habit. Just don’t swap the argument order when you move from one to the other.
Frequently asked questions
Why does my SUMIFS return 0?
No row meets all the conditions at the same time. Check each condition for typos and extra spaces, and make sure a comparison with a cell is written ">="&F2 and not ">=F2".
Can SUMIFS use OR logic?
Not inside one formula: every condition must be true. To add North or South, add two SUMIFS together: =SUMIFS(D2:D13,A2:A13,"North")+SUMIFS(D2:D13,A2:A13,"South").
How do I add values between two dates or numbers?
Test the same column twice, once per bound: =SUMIFS(D2:D13,D2:D13,">=500",D2:D13,"<1000"). With bounds in cells, write ">="&F2 and "<"&G2.
Is SUMIFS case-sensitive?
No. "north", "North" and "NORTH" all match the same cells.
Keep practising
All 5 SUMIFS exercises, with explanations and progress tracking.
Open the SUMIFS practice set