Totals and counts function
MINIFS
The smallest number among the matching rows.
Purpose
Find the smallest figure among the rows that qualify
Returns
The smallest matching number, or nought if nothing matched
Syntax
=MINIFS(min_range, range1, condition1, [range2], [condition2], ...)
| Argument | Required | What goes here |
|---|---|---|
min_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 MINIFS
MINIFS is MAXIFS from the other end, with the same argument order: the column to search, then range and condition in pairs.
It finds the smallest item within a group, which on a ledger is how a write-off threshold gets tested.
As with MAXIFS, no match gives nought rather than an error.
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 |
|---|---|
=MINIFS(E2:E9,C2:C9,"Overdue")The smallest overdue invoice, which is the one most likely to cost more to chase than it recovers. | 215 |
=MINIFS(D2:D9,C2:C9,"Overdue")The newest of the overdue debts. | 12 |
=MINIFS(E2:E9,B2:B9,"Meridian Freight")Narrowed to one supplier. | 580 |
Notes
- No match gives nought, which for a minimum can look entirely believable.