An incident log mixes planned maintenance with genuine breakdowns, and the biggest number in it is usually a planned shutdown nobody needs to explain. MAXIFS takes the largest value only from the rows that pass every condition you give it, so one formula can ask for the worst breakdown at each site and ignore the rest.
You are the operations analyst for a parcel distribution network, preparing the October reliability review. The incident log in A2:D11 records every time a site's sorting line stopped, whether it was a planned maintenance window or an unplanned breakdown, and for how many minutes. In G2, use MAXIFS to return the longest unplanned outage at the site in F2, then copy it down to G5. Planned stoppages must not count.
Solve it on your own to keep the bonus. Each hint gets one step closer to the formula.
This is the grid you start with. Cell references in the task — B6, C2 — point at the row numbers and column letters below.
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Date | Site | Type | Minutes Down | Site | Longest Unplanned (min) | |
| 2 | 10/2/2026 | Leeds | Unplanned | 42 | Leeds | ||
| 3 | 10/4/2026 | Bristol | Planned | 180 | Bristol | ||
| 4 | 10/7/2026 | Glasgow | Unplanned | 15 | Glasgow | ||
| 5 | 10/9/2026 | Leeds | Planned | 240 | Cardiff | ||
| 6 | 10/13/2026 | Cardiff | Planned | 90 | |||
| 7 | 10/15/2026 | Bristol | Unplanned | 67 | |||
| 8 | 10/20/2026 | Leeds | Unplanned | 118 | |||
| 9 | 10/23/2026 | Glasgow | Unplanned | 54 | |||
| 10 | 10/27/2026 | Bristol | Unplanned | 31 | |||
| 11 | 10/29/2026 | Cardiff | Planned | 120 |
The review is about failures, and the single biggest number in the log is not one: Leeds' 240 minutes and Bristol's 180 were maintenance windows booked weeks in advance. A MAXIFS with only the site condition returns exactly those, so the formula looks right and reports a planned shutdown as each site's worst breakdown. Adding the second pair, column C against "Unplanned", means a row has to pass both tests before its minutes are even considered, and the largest of what is left is the answer: 118 at Leeds, 67 at Bristol, 54 at Glasgow. Glasgow is the useful check here, because both its incidents were unplanned and so the second condition changes nothing; the condition only shows its worth on sites that mix the two. Cardiff returns 0, and that is a real answer, not an error: MAXIFS gives 0 when no row passes every condition, which here means Cardiff had no unplanned outages at all in October. The flip side is that 0 is also what you would see if the site name in F5 were misspelled, so a 0 is worth a glance at the log before it goes in the report as a clean month. The $ signs keep the three log ranges fixed while F2 moves down with the formula, so one formula serves every site.