How to find the minimum value with criteria in Excel (MINIFS)
Formula (Excel and Google Sheets)
=MINIFS(C2:C8,A2:A8,F1)Use MINIFS: =MINIFS(C2:C8,A2:A8,F1) returns the smallest value on the rows that match.
- Result on the example
- $0.39 in
F2 - Works in
- Excel 2019 and later, including Microsoft 365
- Google Sheets
- Same formula
See it work
The formula is already in F2. Change any input and the result updates, or edit the formula to experiment.
How it works
MINIFS keeps the rows whose item is "Bolts M8" and returns the smallest quote among them: Brava's $0.39. The cheaper washers are ignored because they don't match the condition.
=MINIFS(C2:C8,A2:A8,F1)Result0.39
C2:C87 rows × 1 col: $0.42, $0.08, $0.39, $0.45, …A range: 7 cells in one column.
A2:A87 rows × 1 col: "Bolts M8", "Washers", "Bolts M8", "Bolts M8", …A range: 7 cells in one column.
F1"Bolts M8"The value in F1.
MINIFS(C2:C8,A2:A8,F1)0.39MINIFS: Smallest value that meets every condition.- min_range
C2:C87 rows × 1 col: $0.42, $0.08, $0.39, $0.45, … - criteria_range1
A2:A87 rows × 1 col: "Bolts M8", "Washers", "Bolts M8", "Bolts M8", … - criteria1
F1"Bolts M8"
- min_range
In Google Sheets
The same formula works in Google Sheets: =MINIFS(C2:C8,A2:A8,F1). We ran it in Google Sheets mode on the same data and it returns $0.39.
Other ways to do it
Each one was run on the same example sheet.
| Formula | Result | When to use it |
|---|---|---|
| =MIN(FILTER(C2:C8,A2:A8=F1)) | $0.39 | Excel 365 and Google Sheets: filter the bolt quotes, then take the minimum. |
Common mistakes
- MINIFS returns 0 when nothing matches, which looks like a free quote. Check with COUNTIF first, or use the FILTER version, which errors instead.
- Blank quotes are ignored, but a quote of 0 entered for "no bid" becomes the minimum. Leave no-bids blank.
Try it yourself
Same data, empty answer cell. Your formula is graded instantly, then re-checked on changed data, so a typed-in number won't pass.
Find the minimum value with criteria in Excel (MINIFS)
F2, find the lowest quote for the item in F1.Questions
Which supplier gave the lowest quote?
Use =XLOOKUP(1,(A2:A8=F1)*(C2:C8=F2),B2:B8).
How do I use two conditions?
Add a pair: =MINIFS(C2:C8,A2:A8,F1,B2:B8,"<>Corto").
Checked by running on 2026-10-30: every formula and result on this page was calculated by the Spreadsheet Practice formula engine, in both Excel and Google Sheets modes.