How to find the minimum value excluding zeros in Excel
Formula (Excel and Google Sheets)
=MINIFS(B2:B7,B2:B7,">0")Use MINIFS with ">0" on the same range: =MINIFS(B2:B7,B2:B7,">0").
- Result on the example
- $22.50 in
E1 - Works in
- Excel 2019 and later, including Microsoft 365
- Google Sheets
- Same formula
See it work
The formula is already in E1. Change any input and the result updates, or edit the formula to experiment.
How it works
MIN would return 0 from the stores that don't stock the item. MINIFS with the condition ">0" on the same range looks only at real prices and returns the lowest: $22.50.
=MINIFS(B2:B7,B2:B7,">0")Result22.5
B2:B76 rows × 1 col: $24.99, $0.00, $22.50, $26.75, …A range: 6 cells in one column.
MINIFS(B2:B7,B2:B7,">0")22.5MINIFS: Smallest value that meets every condition.- min_range
B2:B76 rows × 1 col: $24.99, $0.00, $22.50, $26.75, … - criteria_range1
B2:B76 rows × 1 col: $24.99, $0.00, $22.50, $26.75, … - criteria1
">0"
- min_range
In Google Sheets
The same formula works in Google Sheets: =MINIFS(B2:B7,B2:B7,">0"). We ran it in Google Sheets mode on the same data and it returns $22.50.
Other ways to do it
Each one was run on the same example sheet.
| Formula | Result | When to use it |
|---|---|---|
| =SMALL(B2:B7,COUNTIF(B2:B7,0)+1) | $22.50 | Skips past the zeros by rank: with two zeros, the 3rd smallest value is the lowest real price. Works in every version. |
| =MIN(B2:B7) | $0.00Different result | The plain minimum. Returns one of the zeros: 0. |
Common mistakes
">0"also skips negative numbers. To skip only zeros, use"<>0".- Blank cells are already ignored by MIN; the zeros are the problem. Leaving unknown prices blank avoids it.
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 excluding zeros
E1, find the lowest price in B2:B7, ignoring the stores that show 0 (not stocked).Questions
How do I find the maximum excluding zeros?
MAX already ignores zeros unless every value is 0 or negative. For negatives, use =MAXIFS(B2:B7,B2:B7,"<>0").
How do I return the store with the lowest price?
Use =XLOOKUP(E1,B2:B7,A2:A7).
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.