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
Parameter | Required/ Optional | Data Type | Description |
---|---|---|---|
Arg1 | Required | Range |
The range of cells from count cells. |
Arg2 | Required | Variant | A number, expression, cell reference, or text that defines which cells to count. For example, the expression can be 20, “20”, “>20”, “fruit”, or B2. |
- 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.