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)
The worksheet range where the counting occurs.
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.
countCity = WorkSheetFunction.CountIf(Range(“A2:A10"), “Bucharest")
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.
City = “Bucharest"
countCity = WorksheetFunction.CountIf(Range(“A2:A10"), City)
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’.
city = Range("D2")
countCity = WorksheetFunction.CountIf(Range("A2:A10"), city)
Range("D3") = countCity
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
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.
greater_than = Range("D2")
countCity = WorksheetFunction.CountIf(Range("A2:A10"), ">" & greater_than)
Range("D3") = countCity