MINIFSVISIBLE

For instructions on how to add a function to a workbook refer to the page under Inserting Functions

MINIFS -

'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 MINIFSVISIBLE(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