Min If function by Part number

[Aaron]

Hello,

I have a worksheet that I need a min if function sort of like the sum if
function.
I would like the min of QTY in Column "B" by the parts number I have in
Column "A".
The parts number could repeat many times in Column A.
The inputs are (Range, Critiera, Min range), so in this example I would
input (A:A,A2,B:B)


example

PN |QTY| Min
P123 | 4 | 2
P123 | 5 | 2
P123 | 6 | 2
P568 | 8 | 8
P568 | 9 | 8
P238 | 5 | 5
P238 | 6 | 5
P123 | 2 | 2

Is this possible?

Thanks, AJ

 

[Bob Phillips]

=MIN(IF(A2:A200=A2,B2:B200))

which is an array formula, it should be committed with Ctrl-Shift-Enter, not
just Enter.
Excel will automatically enclose the formula in braces (curly brackets), do
not try to do this manually.
When editing the formula, it must again be array-entered.

Note that you cannot use a whole column in array formulae (prior to excel
2007), but must use an explicit range.

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Aaron]

Is there a way to change this then to look for the end of the page??

 

[Aaron]

WOW. I tried this in and it worked (Thank you) but when I filled down it
crashed excel.... Can this be put in a Functionm macro?

 

[Nigel]

See your other postings asking the same question.

 

[Bob Phillips]

This wouldn't crasj Excel in itself, it must be something else.

Lock the range down with $, but not the cell being tested against.

=MIN(IF($A$2:$A$200=A2,$B$2:$B$200))


--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Aaron]

It just hit me.. I couldn't get that one to work because I was using the
column and not a range.. I never received a responce to why it wasn't
working. That's for pointing that out.

I have been needing help with this for a week now. Below is what I used.
Thanks again guys!!!!

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]

I hit the "Send to Microsoft" and it said I have to upgrade to 2003.
Thanks again!

 

[Bob Phillips]

Well they would wouldn't they? I am surprised it didn't say you had to
upgrade to 2007 <bg>

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)