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
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
' 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 473
Downloads 39
CodeID
DB ID
ANmarAmdeen
615
Revisions
v1.0
Wednesday
May
1
2024