In Excel, the function CountIf is used to count how many times a condition has been met within a worksheet range. However, to use this function while writing VBA code, we need to change it from CountIf to WorksheetFunction.CountIf. The full syntax of the function is as follows:

WorksheetFunction.CountIf (Arg1, Arg2)

Name	Required/Optional	Data type	Description
Arg1	Required	Range	The worksheet range where the counting occurs.
Arg2	Required	Variant	The counting criteria. It can be in the form of a number(35), text (“criteria” or “criteri*” or “criteri?”), expression (“>5”), or cell reference(C5).

Return Value: The CountIf function returns data of type double.

Example 1: Using CountIf with Text

The most common way of utilizing CountIf is to count the occurrence of a condition within a range of cells. In our case, we want to count to occurrence of a city of our choice in the list of cities in range (A2:A10).

We set up a simple procedure to store the value of the count in a variable.

Sub CountIf_Vba()
countCity = WorkSheetFunction.CountIf(Range(“A2:A10”), “Bucharest”)
End Sub

This is the most basic way we could work with CountIf  in VBA. We could definitely take this and expand further on it. First, we could use a variable to store the city that we are interested it, and refer to it in the formula, instead of placing the city directly in there.

Sub CountIf_Vba()
City = “Bucharest”
countCity = WorksheetFunction.CountIf(Range(“A2:A10”), City)
End Sub

Finally, we can choose cells in the worksheet to use as input and output for the counting calculation. So we create labels for these cells as shown below. We will take in the value in cell D2 and we will place the count that results from the calculation in cell D3.

We get the variable ‘city’ from the contents of cell D2. We then use this variable as a criteria in the CountIf function. The result of the CountIf calculation is stored in variable ‘countCity’. Finally, cell D3 receives the value of variable ‘countCity’.

Sub Countif_Vba()
'input
city = Range("D2")
countCity = WorksheetFunction.CountIf(Range("A2:A10"), city)
'output
Range("D3") = countCity
End Sub

Now we can insert a city name in cell D2, run the process, and see the output in cell D3.

Now we can think of a couple of more extensions to create a setup that fits our purposes. For example, we set up the macro to be activated using a button or a keystroke. An even better set up is to activate the macro through worksheet_change event. In this setup, the macro gets activated whenever the contents of cell D2 are directly being changed by the user. This code should go in the sheet module.

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("D2")) Is Nothing = False Then
Call Countif_Vba
End If
End Sub

Example 2: Using CountIf with Numbers

We can use CountIf to get the count for numbers that are greater than a particular number. To do this, we are using a similar setup as the above example, but with numbers instead.

Sub Countif_Vba_Numbers()
'input
greater_than = Range("D2")
countCity = WorksheetFunction.CountIf(Range("A2:A10"), ">" & greater_than)
'output
Range("D3") = countCity
End Sub

The output of the above code is shown below: