I am looking for a function that is a lot like SUMIF function but is Min if.
Returns the min in a range by criteria.
Range, Criteria, and Min range
Any help would be great. Example below
Thanks in advance!

PN |QTY| Min (Needed)
123 | 4 | 2
123 | 5 | 2
123 | 6 | 2
568 | 8 | 8
568 | 9 | 8
238 | 5 | 5
238 | 6 | 5
123 | 2 | 2

Aaron

On Jan 28, 1:58 pm, Aaron wrote:
I am looking for a function that is a lot like SUMIF function but is Min if.
Returns the min in a range by criteria.
Range, Criteria, and Min range
Any help would be great. Example below
Thanks in advance!

PN |QTY| Min (Needed)
123 | 4 | 2
123 | 5 | 2
123 | 6 | 2
568 | 8 | 8
568 | 9 | 8
238 | 5 | 5
238 | 6 | 5
123 | 2 | 2

Aaron

Here's a crude one from my Personal Workbook that I coded up. You can
try it and/or improve it:

SteveM

Public Function MinIf(Ref_Range As Range, Criterion As Variant,
Min_Range As Range) As Single
Dim cell As Range
Dim minVal As Single
Dim iRow As Integer, jCol As Integer, iCount As Integer,
jCount As Integer

iCount = Ref_Range.Rows.Count
jCount = Ref_Range.Columns.Count

minVal = Application.WorksheetFunction.Max(Min_Range)

For iRow = 1 To iCount
For jCol = 1 To jCount
If Ref_Range.Cells(iRow, jCol).Value = Criterion Then
If Min_Range.Cells(iRow, jCol).Value < minVal Then
minVal = Min_Range.Cells(iRow, jCol).Value
End If
End If
Next
Next

MinIf = minVal


End Function

Hi Steve,

I used the below function and got a #value error.
=PERSONAL.XLS!MinIf(A:A,A2,B:B) is what was used on the below example.
Am I doing something wrong?

"SteveM" wrote:

On Jan 28, 1:58 pm, Aaron wrote:
I am looking for a function that is a lot like SUMIF function but is Min if.
Returns the min in a range by criteria.
Range, Criteria, and Min range
Any help would be great. Example below
Thanks in advance!

PN |QTY| Min (Needed)
123 | 4 | 2
123 | 5 | 2
123 | 6 | 2
568 | 8 | 8
568 | 9 | 8
238 | 5 | 5
238 | 6 | 5
123 | 2 | 2

Aaron

Here's a crude one from my Personal Workbook that I coded up. You can
try it and/or improve it:

SteveM

Public Function MinIf(Ref_Range As Range, Criterion As Variant,
Min_Range As Range) As Single
Dim cell As Range
Dim minVal As Single
Dim iRow As Integer, jCol As Integer, iCount As Integer,
jCount As Integer

iCount = Ref_Range.Rows.Count
jCount = Ref_Range.Columns.Count

minVal = Application.WorksheetFunction.Max(Min_Range)

For iRow = 1 To iCount
For jCol = 1 To jCount
If Ref_Range.Cells(iRow, jCol).Value = Criterion Then
If Min_Range.Cells(iRow, jCol).Value < minVal Then
minVal = Min_Range.Cells(iRow, jCol).Value
End If
End If
Next
Next

MinIf = minVal


End Function