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
© 2022 Better Solutions Limited. All Rights Reserved. © 2022 Better Solutions Limited TopPrevNext