IntermediateTotals and maths
Syntax
=SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...)| Argument | What it means |
|---|---|
sum_range | The numbers to add up. Note that it comes first. |
criteria_range1, criteria1 | The first range to test and its condition. |
criteria_range2, criteria2 | Optional. More pairs. All must be true. |
Examples with results
Every result below is calculated by the practice engine on the sample data shown, not typed in by hand.
Office supplies sales (fictional)
| Row | A | B | C | D | E |
|---|---|---|---|---|---|
| 1 | Item | Qty | Unit price | Region | Date |
| 2 | Pen | 10 | 1.5 | North | 2025-01-15 |
| 3 | Notebook | 5 | 12 | South | 2025-02-03 |
| 4 | Pen | 20 | 1.5 | South | 2025-02-28 |
| 5 | Stapler | 2 | 40 | North | 2025-03-10 |
| 6 | Folder | 15 | 3.25 | East | 2025-03-22 |
| 7 | Notebook | 8 | 12 | East | 2025-04-05 |
| 8 | Pen | 12 | 1.5 | North | 2025-04-18 |
| 9 | Marker | 30 | 2 | South | 2025-05-02 |
=SUMIFS(B2:B9,A2:A9,"Pen",D2:D9,"South")Pen quantity sold in the South
=SUMIFS(B2:B9,D2:D9,"North",B2:B9,">=10")North orders of 10 units or more
Try it yourself
Change the formula or switch to other sample data.
Press Calculate to see the result.
The practice box runs in your browser with BDK’s own calculator, which covers about 60 common functions. Cell references work as in Excel (B2, B2:B9). Your formulas are not saved or sent anywhere. For anything unusual, check the result in Excel itself.
How people use SUMIFS at work
- Finance and accounting
Total unpaid for one customer above a limit.
Data: Customer invoices (fictional)=SUMIFS(C2:C6,B2:B6,"Harbour Cafe",C2:C6,">1000") - Sales
Regional totals for one product.
Data: Office supplies sales (fictional)=SUMIFS(B2:B9,A2:A9,"Notebook",D2:D9,"East")
Common mistakes and tips
- In SUMIFS the sum range is the first argument, but in SUMIF it is the last.
- All ranges must have the same number of rows.
Works in
All versions of Excel, and Google Sheets.
Related formulas
Lessons that use it
Need Something Different? Let’s Talk.
Looking for different features or something built around your workflow? Share your idea with us.