How to Use CountIf in VBA

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).

cities list

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.

setting up the countif

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()
city = Range("D2")
countCity = WorksheetFunction.CountIf(Range("A2:A10"), city)
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.

string text to count
code for countif
countif results for string count

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.

counting by number instead
Sub Countif_Vba_Numbers()
greater_than = Range("D2")
countCity = WorksheetFunction.CountIf(Range("A2:A10"), ">" & greater_than)
Range("D3") = countCity
End Sub

The output of the above code is shown below:

results of counting by number

Leave a Reply

Your email address will not be published. Required fields are marked *