VBA COUNTIF Function in Excel: 6 Examples

Method 1 – WorksheetFunction with COUNTIF in Excel VBA

Steps:

Press Alt + F11 on your keyboard or go to the tab Developer -> Visual Basic to open Visual Basic Editor.

In the pop-up code window, from the menu bar, click Insert -> Module.

Copy the following code and paste it into the code window.

Sub ExCOUNTIF()
    Range("B13") = Application.WorksheetFunction.CountIf(Range("B5:B10"), "<3")
End Sub

Your code is now ready to run.

Press F5 on your keyboard or from the menu bar select Run -> Run Sub/UserForm. Or click on the small Play icon in the sub-menu bar to run the macro.

We wanted to find out how many numbers in our dataset are less than 3. So, after running the code, we got the result of 4, which is the count of numbers that are less than 3 for our dataset.

Method 2 – COUNTIF Function to Count a Specific Text in Excel

Steps:

Open Visual Basic Editor from the Developer tab and Insert a Module in the code window.
Copy the following code and paste it.

Sub CountifText()
'input
countName = WorksheetFunction.CountIf(Range("B5:B10"), "John")
'output
Range("E7") = countName
End Sub

Your code is now ready to run.

Run the macro, and you will get the total count.

If you don’t want to write the text directly in your code, you can store it in a variable first and later pass it inside the code. Just like the code below,

Sub CountifText()
'input
Name = Range("E6")
countName = WorksheetFunction.CountIf(Range("B5:B10"), Name)
'output
Range("E7") = countName
End Sub

Method 3 – COUNTIF Function to Calculate Number with VBA

Steps:

Open Visual Basic Editor from the Developer tab and Insert a Module in the code window.
In the code window, copy the following code and paste it.

Sub CountifNumber()
'input
countNum = WorksheetFunction.CountIf(Range("B5:B10"), ">1.1")
'output
Range("E7") = countNum
End Sub

Your code is now ready to run.

Run the macro, and you will get the total count.

If you don’t want to write the number directly in your code, then you can store it in a variable first and later pass the variable inside the code. Just like the code below,

Sub CountifNumber()
'input
Num = Range("E6")
countNum = WorksheetFunction.CountIf(Range("B5:B10"), ">" & Num)
'output
Range("E7") = countNum
End Sub

Method 4 – COUNTIF Function with a Range of Object in Excel

Steps:

Open Visual Basic Editor from the Developer tab and Insert a Module in the code window.
In the code window, copy the following code and paste it.

Sub ExCountIFRange()
   Dim iRng As Range
'assign the range of cells
   Set iRng = Range("B5:B10")
'use the range in the  formula
   Range("B13") = WorksheetFunction.SumIf(iRng, ">1")
'release the range object
  Set iRng = Nothing
End Sub

Your code is now ready to run.

Run the code, and you will get the total count with a summation value.

5. COUNTIF Formula Method in Excel

Steps:

In the code window of the Visual Basic Editor, copy the following code and paste it.

Option Explicit
Sub ExCountIfFormula()
  Range("B13").Formula = "=COUNTIF(B5:B10, "">1"")"
End Sub

Your code is now ready to run.

This code will give you the total amount of data that you require.

5.2. FormulaR1C1 Method

The FormulaR1C1 method is more flexible as it does not restrict to a set range of cells.

With the same dataset, we will use FormulaR1C1 to count values in VBA.

Steps:

In the code window of the Visual Basic Editor, copy the following code and paste it.

Option Explicit
Sub ExCountIfFormulaRC()
  Range("B13").FormulaR1C1 = "=COUNTIF(R[-8]C:R[-1]C,"">2"")"
End Sub

Your code is now ready to run.

This code will also give you the total amount of data that you require.

If you don’t want to set the output range, you can make this code even more flexible by writing like this,

Option Explicit
Sub ExCountIfFormulaRC()
  ActiveCell.FormulaR1C1 = "=COUNTIF(R[-8]C:R[-1]C,"">2"")"
End Sub

The formula will count the cells that meet the condition and place the answer into the ActiveCell in your worksheet. The Range inside the COUNTIF function must be referred to using the Row (R) and Column (C) syntax.

Method 6 – Assigning Result of the COUNTIF Function to a Variable

Use the result of your formula elsewhere rather than in your Excel dataset; you can assign the result to a variable and use it later in your code.

The VBA code,

Sub AssignCountIfVariable()
   Dim iResult As Double
'Assign the variable
  iResult = Application.WorksheetFunction.CountIf(Range("B5:B10"), "<3")
'Show the result
  MsgBox "The count of cells with value less than 3 is " & iResult
End Sub