 ## 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)`

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: