You can use the following basic syntax to use the MIN function in Excel to find the minimum value in a range while excluding cells that are equal to zero:
=SMALL(B2:B15,COUNTIF($B$2:$B$15,0)+1)
This particular formula finds the minimum value in the cell range B2:B15, excluding any cells that are equal to zero.
This formula works by first using COUNTIF() to count the number of zeros and adding one, then using the SMALL() function to return the kth smallest value not equal to zero.
The following example shows how to use this formula in practice.
Example: How to Use MIN Function and Exclude Zero in Excel
Suppose we have the following dataset in Excel that shows the number of sales made by various employees at some company:
We can use the following formula to find the minimum value in the sales column while excluding all values equal to zero:
=SMALL(B2:B15,COUNTIF($B$2:$B$15,0)+1)
We’ll type this formula into cell D2 and then press Enter:
From the output we can see that the minimum value in the sales column (excluding all values equal to zero) is 2.
Additional Resources
The following tutorials explain how to perform other common tasks in Excel:
Excel: How to Find Max Value by Group
Excel: How to Calculate the Median by Group
Excel: How to Calculate the Average by Group