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.


Download Workbook

You can download the free practice Excel workbook from here.


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.

WorksheetFunction with COUNTIF in Excel VBA

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

Result of WorksheetFunction with COUNTIF in Excel VBA


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.

COUNTIF Function to Count a Specific Text in Excel VBA

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

Result of COUNTIF Function to Count a Specific Text in Excel VBA

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


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.

Result of COUNTIF Function to Calculate Number in Excel VBA

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

COUNTIF Function to Calculate Number in Excel VBA

Read more: How to Use COUNTIF to Count Cells Greater Than 0 in Excel


Similar Readings


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.

COUNTIF Function with a Range of Object in Excel VBA

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

Result of COUNTIF Function with a Range of Object in Excel VBA

Read more: COUNTIF Multiple Ranges Same Criteria 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.

COUNTIF Formula Method in Excel VBA

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

Result of COUNTIF Formula Method in Excel VBA

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.

COUNTIF FormulaR1C1 Method in Excel VBA

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

Result of COUNTIF FormulaR1C1 Method in Excel VBA

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.


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

Assigning Result of the COUNTIF Function to a Variable in Excel VBA

The result will be shown in the Excel message box.

Result of Assigning Result of the COUNTIF Function to a Variable in Excel VBA


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

Sanjida Ahmed

Sanjida Ahmed

Hello World! This is Sanjida, an Engineer who is passionate about researching real-world problems and inventing solutions that haven’t been discovered yet. Here, I try to deliver the results with explanations of Excel-related problems, where most of my interpretations will be provided to you in the form of Visual Basic for Applications (VBA) programming language. Being a programmer and a constant solution seeker, made me interested in assisting the world with top-notch innovations and evaluations of data analysis.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo