VBA COUNTIF Function in Excel (6 Examples)

The COUNTIF function in Excel is used to count the number of cells within a range that meet the given condition. In this article, we will show you how to use the COUNTIF function in Excel with VBA macro.

COUNTIF Function in Excel

• Syntax

WorksheetFunction.CountIf(Arg1 As Range, Arg2) As Double

• Parameters

• Return Type

Value as Double

6 Examples of Using the COUNTIF Function in Excel with VBA

In this section, you will learn how to use the COUNTIF function in Excel to count texts, numbers etc. with VBA code.

1. WorksheetFunction with COUNTIF in Excel VBA

Excel’s WorksheetFunction can be used to call most of the other functions in Excel that are available within the Insert Function dialogue box in Excel and the COUNTIF function is one of those functions.

With the above example, we will learn how to use the WorksheetFunction with COUNTIF to count data with VBA in Excel.

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. You can also just click on the small Play icon in the sub-menu bar to run the macro.

We wanted to find out how many numbers are there in our dataset that 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.

Read More: How to Use COUNTIF Between Two Numbers

2. COUNTIF Function to Count a Specific Text in Excel

If you want to count any specific text such as how many cities or names or foods etc. are there in an Excel sheet, then you can utilize the COUNTIF function in VBA.

From the above example, we will learn how to use the COUNTIF to count how many times the name John occurs in our dataset with VBA macro.

Steps:

• Same way as before, 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 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 then you can store it in a variable first and later pass the variable 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

Read More: Count Text at Start with COUNTIF & LEFT Functions in Excel

3. COUNTIF Function to Calculate Number with VBA

You can utilize the COUNTIF function to extract certain results.

From the above example, we will learn how to use the COUNTIF to count how many numbers are there in our dataset that are greater than 1.1 with VBA macro.

Steps:

• Same way as before, 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.

As discussed before, 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

Read More: Excel COUNTIF with Greater Than and Less Than Criteria

4. COUNTIF Function with a Range of Object in Excel

You can assign a group of cells to the Range Object and then use that Range Object to count values 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.

Read More: How to Use COUNTIF for Non Contiguous Range in Excel

5. COUNTIF Formula Method in Excel

You can also use the Formula and/or FormulaR1C1 method to apply a COUNTIF to a cell in VBA. These methods are more flexible in doing such operations.

5.1. Formula Method

The Formula method allows specifying the range of cells as B5:B10 shown below in the example.

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 piece of code will give you the total count of the 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 now learn how to 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 count of the data that you require.

If you don’t want to set the output range then 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.

Read More: How to Apply COUNTIF Between Two Cell Values in Excel

6. Assigning Result of the COUNTIF Function to a Variable

If you want to 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 for that is,

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.

Read More: COUNTIF Excel Example

Download Workbook

You can download the free practice Excel workbook from here.

Conclusion

This article showed you how to use the COUNTIF function in Excel with VBA. I hope this article has been very beneficial to you. Feel free to ask if you have any questions regarding the topic.

Further Readings

• How to Use the Combination of COUNTIF and SUMIF in Excel
• COUNTIF Function to Count Cells That Are Not Equal to Zero
• How to Use Excel COUNTIF That Does Not Contain Multiple Criteria
• Count Blank Cells with Excel COUNTIF Function: 2 Examples
• How to Use COUNTIF for Date Range in Excel