MINIFSVISIBLE





'rgeCriteria - The range of cells containing the criteria you want to check.
'sCriteria - The criteria value you want to match.
'rgeMinRange - The range of corresponding values you want the minimum of.

Public Function MINIF(ByVal rgeCriteria As Range, _
                      ByVal sCriteria As String, _
                      ByVal rgeMinRange As Range) As Single
          
Dim iconditioncolno As Integer
Dim inumberscolno As Integer
Dim lrowno As Long
Dim sngmin As Single
Dim vcellvalue As Variant

   iconditioncolno = rgeCriteria.Column
   inumberscolno = rgeMinRange.Column
   For lrowno = 1 To rgeCriteria.Rows.Count
      vcellvalue = rgeCriteria.Parent.Cells(rgeCriteria.Row + lrowno - 1, inumberscolno).Value
      If rgeCriteria.Parent.Cells(rgeCriteria.Row + lrowno - 1, iconditioncolno).Value = sCriteria And _
         IsNumeric(vcellvalue) = True Then
         If sngmin = 0 Then sngmin = vcellvalue
         If vcellvalue < sngmin Then sngmin = vcellvalue
      End If
      
      If sngmin <> 0 And IsEmpty(vcellvalue) = True Then Exit For
   Next lrowno
    
   For lrowno = 1 To rgeCriteria.Rows.Count
      vcellvalue = rgeCriteria.Parent.Cells(rgeCriteria.Row + lrowno - 1, inumberscolno).Value
      If rgeCriteria.Parent.Cells(rgeCriteria.Row + lrowno - 1, iconditioncolno).Value = sCriteria And _
         IsNumeric(vcellvalue) = True Then
         If rgeCriteria.Parent.Cells(rgeCriteria.Row + lrowno - 1, inumberscolno).Value < sngmin Then
            sngmin = rgeCriteria.Parent.Cells(rgeCriteria.Row + lrowno - 1, inumberscolno).Value
         End If
      End If
      
      If sngmin <> 0 And IsEmpty(vcellvalue) = True Then Exit For
   Next lrowno
    
   MINIF = sngmin
End Function

© 2020 Better Solutions Limited. All Rights Reserved. © 2020 Better Solutions Limited TopPrevNext