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

• 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``````

• 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``````

• 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``````

• 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``````

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

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

The result will be shown in the Excel message box.

Get FREE Advanced Excel Exercises with Solutions!
Sanjida Ahmed

Sanjida Ahmed, who graduated from Daffodil International University with a degree in Software Engineering, has worked with SOFTEKO since 2021. She has written over 100 articles on Excel & VBA and, since 2022, has worked as the Project Manager of the Excel Extension Development Project in the Software Development Department. Since starting this software development, she has established an outstanding workflow encompassing a full SDLC. She always tries to create a bridge between her skills and interests in... Read Full Bio

We will be happy to hear your thoughts

Advanced Excel Exercises with Solutions PDF