 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / Programming / January 2008Min If
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 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.  SignatureCordially, Chip Pearson Microsoft Most Valuable Professional Excel Product Group, 1998 - 2008 Pearson Software Consulting, LLC www.cpearson.com (email on web site) >I am looking for a function that is a lot like SUMIF function but is Min >if. [quoted text clipped - 4 lines] > > 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 > Aaron, > [quoted text clipped - 19 lines] > > > > Aaron > This doesn't work. Actually what I posted does in fact work. You didn't post adequate detail. SignatureCordially, Chip Pearson Microsoft Most Valuable Professional Excel Product Group, 1998 - 2008 Pearson Software Consulting, LLC www.cpearson.com (email on web site) > 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 [quoted text clipped - 45 lines] >> > >> > 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 > > This doesn't work. > [quoted text clipped - 49 lines] > >> > > >> > Aaron This works if you use a range and not columns (A:A) Thanks, Aaron > Hi Chip, > Sorry, I put more detail to better explain the details. [quoted text clipped - 80 lines] > > >> > > > >> > Aaron Perhaps something like: If(your criteria, Min(A1:P100),"No") HTH Otto >I am looking for a function that is a lot like SUMIF function but is Min >if. [quoted text clipped - 4 lines] > > Aaron |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.