Haldane Interiors signs off any quote discounted more than 15%. The November compliance sheet showed five reps and a deepest discount of 12.0%, so nothing was signed. Seven quotes had gone out between 16.0% and 28.0%.
The sheet is one column of MAXIFS, a row per rep, reading the quote register beside it. Three of its five answers were 0.0%.
One of those zeros was true and two of them meant nothing matched. MAXIFS reports both with the same number, and a zero in a discount column is the most reassuring figure on the sheet.
| November 2026 | Figure |
|---|---|
| Deepest discount reported | 12.0% |
| Deepest discount given | 28.0% |
| Quotes over the 15% ceiling | 7 |
| Sign-offs raised | 0 |
| Discount granted without one | £46,218.00 |
| Still inside quote validity | £7,576.00 |
Scope.
MAXIFSandMINIFSin Excel 2019, 2021, 365 and Excel for the web, withMAX,MIN,COUNTIFS,FILTERand the legacyMAX(IF())array behind them.Excel 2016 and earlier have neither function. Section 11 says what those builds make of a sheet written this way.
Throughout,
Rep,DiscandRaisedare the three columns of the quote register: who raised the quote, the discount it carried, and the date it went out.
01Three Reps Showed 0.0% and Two Were Wrong
Haldane quotes commercial fit-outs off a published price list. A rep may discount up to 15% on their own authority; past that, a director signs the quote before it leaves.
The compliance sheet is five rows, one per rep, and one formula filled down beside them: =MAXIFS(Disc,Rep,$A2). It answers the only question the policy asks, which is what the deepest cut of the month was.
In November it read 12.0% for Marianne Hale, 9.5% for Claire Brightman and 0.0% for the other three. Nobody signed anything, because nothing on the sheet came near the ceiling.
Rohan Desai's 0.0% was true: he quoted seven jobs at list price. The other two zeros were the formula reporting that it had nothing to report, and all three looked identical.
Aleks Nowak's deepest discount was 28.0% and Grant Fenwick's was 22.5%. Between them they sent seven quotes through the ceiling, and five of those have since become orders.
Five Reps, Three Zeros and a Discount Ceiling Nobody Enforced
Haldane Interiors' November discount compliance sheet, in A1:F12. Column A is the rep as the compliance sheet spells them, which is not always how the quoting app spells them. Column C is what `=MAXIFS(Disc,Rep,$A2)` returned for that rep and column D is the deepest discount the register actually holds for them; column E counts the quotes they sent past the 15% ceiling. Three of the five reps show 0.0% in column C and only one of those three is true, which is the whole of the fault. The six rows underneath are the month as reported, the month as checked, and what the seven unsigned quotes cost.
fxCells with formulas are highlighted in green
Hover over formula cells to see the formula and highlight referenced cells
02MAXIFS Takes the Answer Column First
MAXIFS(max_range, criteria_range1, criteria1, …) puts the column you want the maximum from first, then as many criteria pairs as the question needs. That is the shape of SUMIFS, and not the shape of SUMIF.
SUMIF and COUNTIF read the other way round: the range being tested comes first, and the range being added comes last. Swapping the first two arguments of MAXIFS usually returns a zero rather than an error, because a column of names has no maximum.
| Function | First argument | Where the criteria go |
|---|---|---|
MAX | The numbers | Nowhere |
SUMIF | The range tested | Criteria, then the sum range last |
SUMIFS | The numbers added | In pairs after it |
MAXIFS | The numbers measured | In pairs after it |
MINIFS | The numbers measured | In pairs after it |
Every range has to be the same height. A 400-row discount column against a 399-row rep column returns #VALUE!, which is the one failure in this family that announces itself.
Scenario: Write =MAXIFS(Disc,Rep,$A2) beside a rep's name, then swap the first two arguments on purpose and read what comes back. A zero means Excel found no numbers to measure, not that you typed it wrongly.
03No Match Is a Zero, Not #N/A
Lookups report a miss. VLOOKUP and XLOOKUP return #N/A, FILTER returns #CALC!, and an error on a report stops it being read as if it were finished.
MAXIFS does not do that. When no row meets the criteria it returns 0, which is the documented behaviour rather than a bug: there was nothing to measure, and zero is what comes back.
The compliance sheet spells its reps Nowak, A.. The register, which is fed from the quoting app, spells the same person Nowak, Aleks. No row matched, so the answer was zero.
| What the register holds | What MAXIFS returns |
|---|---|
| Eleven quotes, deepest 28.0% | 28.0% |
| The same eleven under another spelling | 0.0% |
| Seven quotes, all at list price | 0.0% |
| No quotes at all | 0.0% |
A zero is a plausible discount, a plausible overdue balance and a plausible variance. That is the whole fault: the failure case and a real answer share a value.
Build the name list out of the register. =SORT(UNIQUE(Rep)) spills the names the data actually holds, so the sheet cannot ask about a spelling that is not there, and a new rep appears on it in the week they start.
Scenario: Point a MAXIFS at a name that appears nowhere in your data by typing one into the criteria cell. The zero it returns is what your report shows the month somebody's spelling changes.
04It Measures Numbers and Ignores the Rest
MAX ignores text, blanks and logical values inside a range it is handed, and MAXIFS inherits that. A discount column holding 22.5% as text is a column MAXIFS can see and will not measure.
Grant Fenwick raises his quotes on the mobile app, which writes percentages as text. All nine of his rows matched the criteria. Not one of them held a number, so the maximum of what matched was zero.
SUM and AVERAGE skip the same cells just as quietly. The difference is that a total of nine missing quotes looks small and a maximum of nine missing quotes looks like compliance.
COUNT and COUNTA tell the two apart. =COUNTA(Disc)-COUNT(Disc) is the number of cells in the column holding something that is not a number, and on a column of figures it should read zero.
Turning text into numbers. Select the column, then Data → Text to Columns → Finish converts it in place.
=VALUE(TRIM(B2))does it in a formula, and Paste Special → Multiply against a cell holding 1 does it without one.Text is not always visible. Excel left-aligns it, but a column formatted right-aligned hides even that, and the green triangle in the corner appears only while background error checking is switched on.
Scenario: Put =COUNTA(Disc)-COUNT(Disc) under your own figures column. Any answer above zero counts cells that your MAXIFS, SUM and AVERAGE are all skipping without saying so.
05Ask Whether Anything Matched At All
The guard is one COUNTIFS in front of the answer:
=IF(COUNTIFS(Rep,$A2)=0,"none",MAXIFS(Disc,Rep,$A2))
COUNTIFS counts the rows that met the criteria whatever those rows contain, so it separates nothing-matched from a maximum of zero. Every criteria pair in the MAXIFS has to go into the COUNTIFS as well, or the guard is answering a different question.
A column with text in it needs a stricter count. =SUMPRODUCT((Rep=$A2)*ISNUMBER(Disc)) counts only the matching rows that hold a number, so it reads zero for both of November's wrong answers and 18 for a rep whose figures are fine.
| Rep | Rows matched | Numbers among them | MAXIFS |
|---|---|---|---|
| Hale, M. | 18 | 18 | 12.0% |
| Nowak, A. | 0 | 0 | 0.0% |
| Fenwick, G. | 9 | 0 | 0.0% |
| Desai, R. | 7 | 7 | 0.0% |
Read down that table and Desai's zero stops looking like the other two. The maximum is the same number in three rows and the evidence beside it is different in every one.
Scenario: Wrap one MAXIFS in your workbook in the IF(COUNTIFS(…)=0,…) guard above, then set its criteria to a name that does not exist. The cell should read none rather than a number.
06MINIFS Dates the Zero 00/01/1900
MINIFS returns zero on a miss in exactly the same way, and a date column is the one place where that shows. Serial zero is the day before Excel's calendar starts, so a cell formatted as a date reads 00/01/1900.
The same sheet reports each rep's earliest quote with =MINIFS(Raised,Rep,$A2). Nowak's row had read 00/01/1900 since January, and nobody acted on it: a date that absurd reads as a formatting fault rather than a rep nothing matched.
That visible zero is the lucky case. The identical miss in a discount column, a price column or a days-late column reads as a discount, a price or a days-late figure, and the only difference between them is the number format.
Scenario: Format a spare cell as a date and type 0 into it. Whatever your regional settings render is what a missed MINIFS looks like in every date column in your workbook.
07Criteria That Compare, Not Just Match
MAXIFS takes the same criteria as SUMIFS: a value to match, or an operator and a value inside quotes. ">0.15" is a condition, ">"&$B$1 is a condition built from a cell with &, and "Nowak*" is a wildcard.
So the sheet can ask the policy's own question instead. =COUNTIFS(Rep,$A2,Disc,">0.15") counts the quotes a rep sent past the ceiling, and it cannot return a figure that reads like a discount.
A month needs two date conditions, each joining the operator to the cell:
=MAXIFS(Disc,Rep,$A2,
Raised,">="&$B$1,
Raised,"<"&EOMONTH($B$1,0)+1)
Why the second condition is
<the first of next month. A quote raised at 16:40 on the 30th is stored as the 30th plus a fraction of a day, which is greater than the 30th.
"<="&EOMONTH($B$1,0)drops every quote raised after midnight on the last day of the month."<"&EOMONTH($B$1,0)+1keeps them.
Scenario: Add =COUNTIFS(Rep,$A2,Disc,">0.15") beside each name on your own sheet. It answers what the ceiling actually asks, and a wrong answer from it cannot be mistaken for a percentage.
08MAX(FILTER()) Says When Nothing Matched
FILTER has the opposite manner. Handed criteria that nothing meets, it returns #CALC!, and the error propagates: =MAX(FILTER(Disc,Rep=$A2)) is #CALC! for a rep who is not in the register.
That is the behaviour a compliance sheet wants. It cannot be signed, it cannot be read as a zero, and =IFERROR(MAX(FILTER(Disc,Rep=$A2)),"none") turns it into a word rather than a figure.
It fixes half of November. Fenwick's nine rows matched, so FILTER returned nine text percentages and MAX ignored all nine, which is zero again. No wrapper rescues a column of text.
| Written as | No row matches | Rows match but hold text |
|---|---|---|
=MAXIFS(Disc,Rep,$A2) | 0 | 0 |
=MAX(FILTER(Disc,Rep=$A2)) | #CALC! | 0 |
=MAX(IF(Rep=$A2,Disc)) | 0 | 0 |
Guarded with COUNTIFS | none | 0 |
Guarded with SUMPRODUCT | none | none |
Scenario: Replace one MAXIFS with =MAX(FILTER(Disc,Rep=$A2)) and point it at a name that is not in the data. #CALC! is the answer you want on a report somebody has to sign.
09The Maximum Is a Number, Not a Quote
Knowing the deepest discount was 28.0% is half the job, because somebody has to open the quote. MAXIFS returns a value and says nothing at all about the row it came from.
=INDEX(Ref,MATCH(1,(Rep=$A2)*(Disc=H2),0)) returns the reference of the quote holding the maximum that H2 reports. XLOOKUP does the same job with the multiplied arrays as its lookup value.
Ties are the catch. Two quotes at 28.0% give one answer and the second never appears. =COUNTIFS(Rep,$A2,Disc,H2) says how many rows hold that maximum, which is the number the formula above hides.
A top three rather than a top one needs LARGE over a filtered list. =LARGE(FILTER(Disc,Rep=$A2),2) is the second deepest discount that rep gave, and LARGE has no criteria argument of its own.
Scenario: Beside your own MAXIFS, add the INDEX and MATCH above and a COUNTIFS of the rows holding that maximum. Open whichever record it names and check it is the one you expected.
10Three Checks That Catch a Fake Zero
Count the rows behind every answer. =COUNTIFS(Rep,$A2) beside each rep turns a column of maxima into a column with its evidence next to it, and a zero in that column is a name nothing matched.
Count the numbers, not the cells. =COUNTA(Disc)-COUNT(Disc) across the whole register reads zero while every figure in it is a figure. One cell would have found Fenwick's nine text percentages.
Reconcile the names. =SUMPRODUCT(--(COUNTIF(Rep,$A$2:$A$6)=0)) counts how many of the five names on the compliance sheet appear nowhere in the register. In November the answer was one.
LET keeps the guarded version readable once a formula carries both: =LET(n,COUNTIFS(Rep,$A2),IF(n=0,"none",MAXIFS(Disc,Rep,$A2))) names the count once and uses it twice.
Scenario: Put those three checks in a row above your summary and format each one to go red when it is not zero. All three would have flagged November on the first of December.
Try it in the grid11Older Excel, the Web and Sheets
MAXIFS and MINIFS arrived in Excel 2019. Open the compliance sheet in 2016 and both come back as #NAME?, which is loud, visible and better than the zero a working build returns.
The replacement in those builds is the array =MAX(IF(Rep=$A2,Disc)), confirmed with Ctrl+Shift+Enter. It carries the same zero on a miss, so it needs the same COUNTIFS in front of it.
Excel for the web and the mobile apps have both functions. Google Sheets has them too and returns zero on a miss in the same way, so a round trip through it changes nothing about this fault.
Scenario: Send one sheet of maxima to a colleague on an older build and ask what they see. #NAME? tells you their Excel is the difference; a number tells you nothing yet.
12Eight Traps
- Reading a zero as an answer.
MAXIFSreturns 0 when nothing matched, and a discount, a variance or a balance of zero is a believable figure. - A criteria spelling that drifted.
Nowak, A.andNowak, Aleksare two different names, and nothing on the sheet says which one the register holds. - Numbers that arrived as text. Every row can match the criteria and still hold nothing
MAXIFSis willing to measure. - A column of negatives. Where every real value is below zero, the no-match zero is the largest number on the sheet.
MINIFSover dates with no guard.00/01/1900reads as a format to fix rather than as a rep whose quotes nothing matched.- Criteria ranges of different heights.
#VALUE!, and the only failure in this family that stops the report being circulated. - Trusting the maximum to name its own row. It returns a value; the quote behind it needs
INDEXandMATCH, and ties need counting. - Sending the workbook to Excel 2016.
#NAME?where the maxima were, which is at least honest about it.
13Five Exercises
- Reproduce it. Build a two-column register, write
=MAXIFS(Disc,Rep,$A2)beside four names, then misspell one of the names and read the answer. - Make the text version. Format a few discount cells as text and retype the figures. Watch the maximum fall to zero with every row still matching the criteria.
- Guard it. Wrap the formula in
=IF(COUNTIFS(Rep,$A2)=0,"none",MAXIFS(Disc,Rep,$A2))and confirm the misspelled name now reads none. - Compare the manners. Put
=MAX(FILTER(Disc,Rep=$A2))beside it and note which of the two failures each formula reports and which it hides. - Find the quote. Add
=INDEX(Ref,MATCH(1,(Rep=$A2)*(Disc=H2),0))and=COUNTIFS(Rep,$A2,Disc,H2), then create a tie and watch the count move to two.
What to Take Away
Every figure on the November compliance sheet came out of the quote register. Five reps, 59 quotes, and a deepest discount of 12.0% that was true of the three rows the formula could measure.
The other two rows were MAXIFS saying it had nothing to measure, in the only language it has for that, which is a zero. One name the register spells differently and nine percentages stored as text.
A lookup tells you when it found nothing. A conditional maximum does not, so the count has to sit beside it: COUNTIFS for the rows, COUNT against COUNTA for the numbers among them.
Seven quotes went out past a ceiling nobody was told about, £46,218.00 of discount with them, and £38,642.00 of that is inside orders the customers have already accepted.