Apply Countif function in vba excel – debugvba.com

Countif function vba excel

Objective

To create the countif formula using VBA excel.

Approach

In this VBA Excel automation, we had data in cell A2:B6. Here wanted to find the count of IDs in column A, which have zone as “North” in column B. We have applied the formula in cell D2 as seen in the image below. We have used the formula property of range object.

Code

In the code below, we had declared 2 variables (1) CellRange (2) CountCriteria. We can also get the values of these 2 variables by user input to make the code more dynamic. But for demonstration purpose we shall initialize them in the code itself.

Sub CountifFormula()
Dim CellRange As String
Dim CountCriteria As String

CellRange = "A2:B6"
CountCriteria = "North"

Range("D2").Formula = "=countif( " & CellRange & " ," & """" & CountCriteria & """" & ")"

End Sub

 

As we can see in the image that string “North” has appeared 2 times in column B, one time for SID 10 and one time for SID 11 . So, the result is 2 which is showcased in cell D2.

 

Countif function vba excel

 

Note

  1. Formula can be seen in the formula bar.
  2. We have used 2 variables to make the code more dynamic.


Reference

https://docs.microsoft.com/en-us/office/vba/api/excel.range.formula

Post you may like

Apply sum function in VBA Excel