How to Find Minimum Value That Is Greater Than 0 in Excel

Finding the smallest value other than zero can be cumbersome if we consider a large dataset. However, we can use the LARGE, COUNTIF, SMALL, COUNTIFS, AGGREGATE functions, Array formula, and Excel VBA to easily determine the values.

Method 1 – Using Excel Array Formula to Get Minimum Value That Greater Than Zero

Steps:

Enter the following array formula in cell D11:

=MIN(IF(C5:C9>0,C5:C9))

Press Ctrl + Shift + Enter since this is an array formula.

We obtain the smallest sale excluding $0.00, which is $8.00.

Formula Breakdown

IF(C5:C9>0, C5:C9) specifies the logical condition in range C5:C9 where the value is greater than 0.
MIN(IF(C5:C9>0, C5:C9)) returns the minimum value greater than 0.

Read More: How to Find Minimum Value in Excel

Method 2 – Combining LARGE and COUNTIF Functions to Find Minimum Value That Is Larger Than 0 in Excel

The LARGE function returns the K-th largest value in a dataset where K is a positive integer. The COUNTIF function returns the total number of occurrences of a specific value.

Steps:

Enter the combined formula below in cell D11:

=LARGE(C5:C9,COUNTIF(C5:C9,">0"))

Press the Enter or Tab keys to see the output.

We obtain our desired result, $8.00.

Formula Breakdown

COUNTIF(C5:C9,”>0″) counts the occurrences of the values that are greater than 0 in column C.
LARGE(C5:C9, COUNTIF(C5:C9,”>0″)) returns the minimum number greater than zero.

Method 3 – Merging SMALL and COUNTIFS Functions to Display Smallest Value Greater Than 0

The SMALL function returns the n-th smallest value in a dataset. The COUNTIFS function returns the total occurrences of a given value according to multiple criteria.

Steps:

In cell D11, enter the following combined formula:

=SMALL(C5:C9,COUNTIFS(C5:C9,0)+1)

Press the Enter key.

The smallest value other than zero, $8.00, is returned.

Formula Breakdown

COUNTIFS(C5:C9,0) counts the total number of cells containing 0 in the range C5:C9.
SMALL(C5:C9, COUNTIFS(C5:C9,0)+1) specifies the greater values than zero and returns 1 output.

Method 4 – Using the AGGREGATE Function to Look for Minimum Value That Is Larger than Zero in Excel

The AGGREGATE function includes different functions like AVERAGE, COUNT, MAX, MIN, SUM, PRODUCT, LARGE, etc. with the Option to omit hidden rows and error values to get certain results. In this case, we will use the AGGREGATE function number 14 (the LARGE function) and Option 6 which represents omitting error values.

Steps:

Enter the formula below in cell D11:

=1/AGGREGATE(14,6,1/C5:C9,1)

Here, AGGREGATE (14, 6) represents the LARGE function with the Omit Error Value Option.
Press Enter to return the output.

Method 5. Running a VBA Code to Determine Minimum Value That Is Greater than 0

Steps:

Go to the Developer tab and click Visual Basic (or press ALT + F11).

The Visual Basic window pops up.

Click the Insert tab and then Module to create a module box.

In the Module box, enter the following VBA code:

Sub SOFTekoMinVALUE()
ActiveCell.FormulaR1C1 = _
"=SMALL(R[-6]C[-1]:R[-2]C[-1],COUNTIFS(R[-6]C[-1]:R[-2]C[-1],0)+1)"
MsgBox "Minimum Value Greater than Zero is " _
& Worksheets("Excel VBA").Range("D11")
End Sub