VBA COUNTIF

VBA COUNTIF

Criteria based functions are the rulers of excel in calculations. At the beginning of the learning of excel, we must have learned the COUTNIF process in excel. In our earlier articles, we have shown you how to work with the COUNTIF function in Excel VBA.

Refer to our article on COUNTIF Formula in Excel to know the basics of COUNTIF function in Excel VBA. In this article, we will show you how to use the same function in VBA coding. Now we will see the same formula in VBA. First thing first, COUNTIF is not a VBA function; instead, it is a worksheet function that can be accessed under the worksheet function class.

VBA COUNTIF

You are free to use this image on your website, templates etc, Please provide us with an attribution linkHow to Provide Attribution?Article Link to be Hyperlinked
For eg:
Source: VBA COUNTIF (wallstreetmojo.com)

Example of Excel VBA Countif Function

Ok, let’s see the simple example.

You can download this VBA Countif Function Excel Template here – VBA Countif Function Excel Template

Look at the below same example of counting values from the lot.

VBA COUNTIF Example 1

In the above image, we have city names from cell A1 to A10. In cell C3, we need to count how many times the city name “Bangalore” appearing in the range A1 to A10.

Ok, follow the below steps to write the code to apply the COUNTIF function.

Step 1: Start the Sub procedure.

Code:

Option Explicit

  Sub Countif_Example1()

End Sub
VBA COUNTIF Example 1-1

Step 2: Since we need to store the result in cell C3, start the Range(“C3”).Value.

Code:

Sub Countif_Example1()

  Range("C3").Value =

End Sub
VBA COUNTIF Example 1-2

Step 3: In cell C3, by applying excel VBA COUNTIF function, we are trying to arrive at the result. So to access the function, we need first to use the Worksheet Function class.

Code:

Sub Countif_Example1()

  Range("C3").Value = WorksheetFunction.

End Sub
VBA COUNTIF Example 1-3

Step 4: From the lost select excel VBA COUNTIF function.

Code:

Sub Countif_Example1()

  Range("C3").Value = WorksheetFunction.CountIf(

End Sub
VBA COUNTIF Example 1-4

Step 5: If you look at the parameters of the VBA COUNTIF function, we don’t see the parameter, as we see in the worksheet.

VBA COUNTIF Example 1-5

As we can see in the above image in the worksheet, we have exact syntax, but in VBA, we can see only Arg 1 & Arg 2.

Arg 1 is Range, so select the range as A1 to A10.

Code:

Sub Countif_Example1()

  Range("C3").Value = WorksheetFunction.CountIf(Range("A1:A10"),

End Sub
Example 1-6

Step 6: Arg 2 is what is the value we need to count from the range A1 to A10. In this example, we need to calculate “Bangalore.”

Code:

Sub Countif_Example1()

  Range("C3").Value = WorksheetFunction.CountIf(Range("A1:A10"), "Bangalore")

End Sub
Example 1-7

Ok, we are done.

Run the code to see the result in cell C3.

Example 1-8

We got the result as 4. Since the city name “Bangalore” appearing in cell A1, A4, A7, and A10 VBA COUNTIF function returned the product as 4.

If you can see VBA code has returned only the result of the formula, we don’t get to know the procedure in the formula bar.

Example 1-9

To arrive at the formula, we need to write the code slightly differently. Below is the code for you to apply the formula itself to the cell.

Code:

Sub Countif_Example1()

  Range("C3").Formula = "=CountIf(A1:A10, ""Bangalore"")"

End Sub

This will apply the formula to the cell C3.

Example 1-10

Arrive Result with Variables

Variables are an integral part of any coding language. We need to declare variables to work efficiently with the VBA codeVBA CodeVBA code refers to a set of instructions written by the user in the Visual Basic Applications programming language on a Visual Basic Editor (VBE) to perform a specific task.read more. For example, look at the below code.

Code:

Sub Countif_Example2()

  Dim ValuesRange As Range
  Dim ResultCell As Range
  Dim CriteriaValue As String

  Set ValuesRange = Range("A1:A10")
  Set ResultCell = Range("C3")

  CriteriaValue = "Bangalore"

  ResultCell = WorksheetFunction.CountIf(ValuesRange, CriteriaValue)

End Sub

Let me decode the code for you to understand better.

Firstly I have declared the two variables as Range.

Dim ValuesRange As Range: This is to reference the list of values.

Dim ResultCell As Range: This to reference the result cell.

Then I have set the range of references to both the variables.

Set ValuesRange = Range(“A1: A10”): This is the range where all the city names are there.

Set ResultCell = Range(“C3”): In this cell, we will store the result of the COUNTIF function.

In the meantime, I have declared one more variable to store the criteria value.

Dim CriteriaValue As String

CriteriaValue = “Bangalore”

So now the variable “CriteteriaValue” holds the value “Bangalore.”

In the next line, as usual, I have applied the COUTNIF function.

ResultCell = WorksheetFunction.CountIf(ValuesRange, CriteriaValue)

Like this, we can apply the COUNTIF function in Excel VBA to fit our needs.

Recommended Articles

This has been a guide to VBA COUNTIF. Here we look at the working of COUNTIF Function in Excel VBA along with practical examples and a downloadable excel template. Below are some useful articles related to VBA –

  • 3 Courses
  • 12 Hands-on Projects
  • 43+ Hours
  • Full Lifetime Access
  • Certificate of Completion
LEARN MORE >>