How to use Countifs formula in VBA?

Question

I am working on a workbook where there are 8 worksheets. Names of worksheets:

[Hierarchy, wins, outlook, pcv, misses, losses, backdate, login].

In "Hierarchy" worksheet I want to apply the formula in a column B, up to the last value of that column B (which includes names of sales person). (I guess we will use a loop, I'm not sure which loop should I use.)

=COUNTIFS(wins!$AL:$AL,Hierarchy!$B4,wins!$P:$P,"Complete")

PS: I need help in above countif formula and loop (in VBA) to use that formula up to the last record in the column.

Which cell is the given formula supposed to be in? It's hard to understand it, because you said Column B, but there is a criteria in the same formula which refers to the cell B4 - same column! That does not make sense from the first glance. Or is it that you don't really want to put that formula in any cell, you only need to get the result? — ZygD
are you just looking to apply the formula down column B until the last cell in the row of data for a particular column? If so, no need to loop. You can possibly use the .FillDown method or the .AutoFill method. You may also want to get the Last Used Rows for the column you need. See this for help with that, because there are caveats to this: rondebruin.nl/win/s9/win005.htm — Scott Holtzman

ZygD ZygD · Accepted Answer · 2015-09-01T14:04:11

If you just need a result as opposed to filling formulas down the column in a worksheet, you could use one of these options:

Fast one - only using loops:

Sub countifs_in_vba()

Dim Result As Long
Dim i As Long
Dim cell As Range
Dim wsHierarchy As Worksheet
Dim wsWins As Worksheet
Set wsHierarchy = ThisWorkbook.Sheets("Hierarchy")
Set wsWins = ThisWorkbook.Sheets("wins")

For Each cell In Intersect(wsHierarchy.Range("B:B"), wsHierarchy.UsedRange)
    For i = 1 To wsWins.Cells.SpecialCells(xlCellTypeLastCell).Row
        If cell.Value = wsWins.Cells(i, "AL").Value And wsWins.Cells(i, "P").Value = "Complete" Then
            Result = Result + 1
        End If
    Next
Next
MsgBox Result

End Sub

Slower one - employing Application.WorksheetFunction:

Sub countifs_in_vba2()

Dim Result As Long
Dim cell As Range
Dim wsHierarchy As Worksheet
Dim wsWins As Worksheet
Set wsHierarchy = ThisWorkbook.Sheets("Hierarchy")
Set wsWins = ThisWorkbook.Sheets("wins")

For Each cell In Intersect(wsHierarchy.Range("B:B"), wsHierarchy.UsedRange)
    Result = Result + Application.WorksheetFunction.CountIfs(wsWins.Range("AL:AL"), cell.Value, wsWins.Range("P:P"), "Complete")
Next
MsgBox Result

End Sub

How to use Countifs formula in VBA?

1 Answers