Totals and counts function
SUMIF
Total the matching rows.
Purpose
Total the rows that meet one condition
Returns
The total of the matching rows
Syntax
=SUMIF(range, condition, [sum_range])
| Argument | Required | What goes here |
|---|---|---|
range | Required | The column to test. |
condition | Required | What that column has to match. |
sum_range | Optional | The column of amounts to add. The tested column is used if you leave it out. |
How to use SUMIF
SUMIF takes the column to test, the condition, and the column to add. Leave the third out and it adds the column it tested, which is what you want when the test and the amounts are the same figures.
The order is worth memorising because SUMIFS reverses it: SUMIF tests first and adds last, SUMIFS adds first and tests after.
The tested column and the added column have to cover the same rows, or the amounts come off rows that were never tested.
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 |
|---|---|
=SUMIF(C2:C9,"Overdue",E2:E9)How much of the ledger is overdue, rather than how many invoices are. | 5685 |
=SUMIF(D2:D9,">60",E2:E9)The value sitting past sixty days, which is the figure a provision is usually built on. | 3770 |
=SUMIF(E2:E9,">1000")With no third argument, the tested column is the one added. | 4950 |
=SUMIF(B2:B9,"Brightwell Paper",E2:E9)One supplier's exposure. This is what a pivot table is doing when it groups by supplier. | 1700 |
Notes
- SUMIF tests first, SUMIFS adds first. Getting the two the wrong way round is the usual mistake.
- The two ranges must cover the same rows.
Related
Practise SUMIF in Unit 08
A reference tells you what it does. A unit makes you use it.