Totals and counts function
MAXIFS
The largest number among the matching rows.
Purpose
Find the largest figure among the rows that qualify
Returns
The largest matching number, or nought if nothing matched
Syntax
=MAXIFS(max_range, range1, condition1, [range2], [condition2], ...)
| Argument | Required | What goes here |
|---|---|---|
max_range | Required | The column of amounts to look at. |
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 MAXIFS
MAXIFS puts the column to search first, then range and condition in pairs, like SUMIFS.
It answers the exposure question with the population narrowed: the largest invoice that is actually overdue, rather than the largest invoice.
When nothing matches it returns nought rather than an error, which is worth remembering because nought is a plausible looking answer for an amount.
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 |
|---|---|
=MAXIFS(E2:E9,C2:C9,"Overdue")The largest overdue invoice. | 2450 |
=MAXIFS(E2:E9,D2:D9,">60")The largest of the debts past sixty days. | 2450 |
=MAXIFS(E2:E9,C2:C9,"Written off")No row has that status, so the answer is nought. Nothing distinguishes it from a genuine nought. | 0 |
Notes
- No match gives nought. Count the matching rows alongside it if that would be misleading.