SUMIFS
Total the rows where every condition holds.
Total the rows where every condition holds
The total of the rows that match all the conditions
Syntax
=SUMIFS(sum_range, range1, condition1, [range2], [condition2], ...)
| Argument | Required | What goes here |
|---|---|---|
sum_range | Required | The column of amounts to add. |
range1 | Required | The first column to test. |
condition1 | Required | What that column has to match. |
range2 | Optional | The next column to test. |
condition2 | Optional | What that column has to match. |
How to use SUMIFS
SUMIFS puts the column being added first, then range and condition in pairs. That is the reverse of SUMIF, and mixing them up is the commonest thing that goes wrong here.
Every range has to cover the same rows, the added column included.
This is what a pivot table does. A pivot grouped by supplier and status is a block of SUMIFS with the labels down the side and along the top, and writing it out is how you can see what it is claiming.
One pair of conditions behaves exactly like SUMIF, so there is no reason not to start with SUMIFS and add pairs as the question gets sharper.
Examples
Every result below is worked out by the same evaluator that marks your answers, on the sheet shown here.
| A | B | C | D | E | F | G | H | |
|---|---|---|---|---|---|---|---|---|
| 1 | Invoice | Supplier | Status | Days overdue | Amount | Supplier | Terms (days) | |
| 2 | INV-8801 | Brightwell Paper | Overdue | 12 | 940 | Ashgrove Catering | 30 | |
| 3 | INV-8802 | Calder Utilities | Queried | 47 | 1180 | Brightwell Paper | 30 | |
| 4 | INV-8803 | Meridian Freight | Overdue | 63 | 2450 | Calder Utilities | 14 | |
| 5 | INV-8804 | Ashgrove Catering | On terms | 0 | 405 | Meridian Freight | 45 | |
| 6 | INV-8805 | Brightwell Paper | Overdue | 38 | 760 | Thornbury Print | 60 | |
| 7 | INV-8806 | Thornbury Print | Overdue | 91 | 1320 | |||
| 8 | INV-8807 | Meridian Freight | On terms | 0 | 580 | |||
| 9 | INV-8808 | Calder Utilities | Overdue | 24 | 215 |
| Formula | Result |
|---|---|
=SUMIFS(E2:E9,C2:C9,"Overdue",D2:D9,">30")Overdue and more than thirty days old. The amount column comes first. | 4530 |
=SUMIFS(E2:E9,B2:B9,"Meridian Freight")One condition, which is SUMIF written the other way round. | 3030 |
=SUMIFS(E2:E9,B2:B9,"Calder Utilities",C2:C9,"Overdue")One supplier, one status. The other Calder invoice is queried rather than overdue, so it drops out. | 215 |
=SUMIFS(E2:E9,D2:D9,">30",D2:D9,"<=60")The same column tested twice, which is how a band with two edges is written. | 1940 |
Notes
- SUMIFS starts with the column to add. SUMIF ends with it.
- Every range must be the same size, including the one being added.
Related
A reference tells you what it does. A unit makes you use it.