Min If

[Aaron]

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.
Thanks in advance!

Aaron

 

[Chip Pearson]

Aaron,

The following array formula will return the minimum value from C3:C13 where
the corresponding entry in B3:B13 is equal to the value in cell C1. If the
value in C1 is not found in B3:B13, the result is #N/A.

=IF(COUNTIF(B3:B13,C1)=0,NA(),MIN(IF(B3:B13=C1,C3:C13,FALSE)))


This is an array formula, so you must press CTRL SHIFT ENTER rather than
just ENTER when you first enter the formula and whenever you edit it later.
If you do this properly, Excel will display the formula in the formula bar
enclosed in curly braces { }. See
http://www.cpearson.com/Excel/ArrayFormulas.aspx for more info about array
formulas.


--
Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group, 1998 - 2008
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)

 

[Otto Moehrbach]

Perhaps something like:
If(your criteria, Min(A1100),"No")
HTH Otto

 

[Aaron]

This doesn't work. The data in the critera range has many of the same and
different values that I need a min against. The only solution that I have
been able to come up with is a pivot table on Min and then vlookup it back to
the spreadsheet. That is why I am looking for something in VB. Example below



example

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

Thanks, Aaron

 

[Chip Pearson]

This doesn't work.

Actually what I posted does in fact work. You didn't post adequate detail.

--
Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group, 1998 - 2008
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)

 

[Aaron]

Hi Chip,
Sorry, I put more detail to better explain the details.
I also found this (Below) but get a #value when it is ran.
What am I doing wrong? Please help.


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

 

[Aaron]

This works if you use a range and not columns (A:A)
Thanks,
Aaron