Descriptive statistics
MINIFS and MAXIFS: find lowest and highest values by category
Compare the lowest and highest supplier quotes within one material group, and avoid mistaking a 0 unmatched result for a free quote.
Supplier quotes across two material categories
Five bids list Material in column A and Quote per tonne in column B. You want the cheapest and highest bid for Timber (selected in D2) without mixing in Steel prices.
| Material (A) | Quote (B) |
|---|---|
| Timber | 145 |
| Steel | 290 |
| Timber | 132 |
| Timber | 158 |
| Steel | 310 |
Extract category minimum, maximum, and spread
MINIFS(B2:B6, A2:A6, D2) evaluates only the Timber rows (145, 132, 158) and returns 132. MAXIFS(B2:B6, A2:A6, D2) returns 158, giving a Timber quote spread of 158 - 132 = 26.
=MINIFS(B2:B6,A2:A6,D2)The zero-return trap when no rows match
Unlike AVERAGEIFS (which returns #DIV/0! when nothing matches), both MINIFS and MAXIFS return 0 when zero rows satisfy the criteria. On a lowest-bid report, 0 looks dangerously like a free quote.
Guard with COUNTIF first so an absent category like Copper reports "No quotes" instead of 0.
=IF(COUNTIF(A2:A6,D2)=0,"No quotes",MINIFS(B2:B6,A2:A6,D2))Try the example
Use the original values or change them to test your understanding.