CountColumnCells_2Criteria

Counts how many cells matching 2 criterias.
Just like CountColumnCells_Criteria but works for two criterias instead of one.
Beauty here is that it works alone without needing any other functions. Pure Excel/VBA

CodeFunctionName
What is this?

Public

Tested

Original Work
Function CountColumnCells_2Criteria(ColumnName, Criteria, Optional Column2Name = "", Optional Criteria2 = "", Optional WB = "This", Optional Shee = "Active", Optional StartFromRow = 1)
    ' Condition = "#" means only numbers
    '            = "" means all
    '            = " >0" numbers greator than 0
    '
    Dim SearchRR As Range, Search2RR As Range
    If WB = "This" Then WB = ThisWorkbook.Name
    If WB = "Active" Then WB = ActiveWorkbook.Name
    If Shee = "Active" Then Shee = ActiveSheet.Name
    If ColumnName = "" Then ColumnName = "A"
    Set SearchRR = Workbooks(WB).Worksheets(Shee).Range(ColumnName & 1).EntireColumn
    If Column2Name > "" Then Set Search2RR = Workbooks(WB).Worksheets(Shee).Range(Column2Name & 1).EntireColumn
   
    If StartFromRow > 1 Then
        LastR = Range("A1").EntireColumn.Rows.Count
        Set SearchRR = Workbooks(WB).Worksheets(Shee).Range(ColumnName & StartFromRow, ColumnName & LastR)
        If Column2Name > "" Then Set Search2RR = Workbooks(WB).Worksheets(Shee).Range(Column2Name & StartFromRow, ColumnName & LastR)
    End If
    If Criteria = "#" Then
        Rett = WorksheetFunction.Count(SearchRR)
    ElseIf Criteria = "" Then
        Rett = WorksheetFunction.CountA(SearchRR)
    ElseIf Column2Name > "" And Criteria2 > "" Then
        Rett = WorksheetFunction.CountIfs(SearchRR, Criteria, Search2RR, Criteria2)
    Else
        Rett = WorksheetFunction.CountIf(SearchRR, Criteria)
    End If
    CountColumnCells_2Criteria = Rett
    Set SearchRR = Nothing
    Set Search2RR = Nothing
End Function

ColumnName, Criteria, Optional Column2Name = "", Optional Criteria2 = "", Optional WB = "This", Optional Shee = "Active", Optional StartFromRow = 1

Views 569

Downloads 41

CodeID
DB ID