Range("B1").Value = WorksheetFunction.CountIf(Range("A2:A11"), ">5000") counts the cells in A2:A11 above 5,000. The criteria is text in quotes, as in a worksheet formula: "East" for an exact match, "*North*" for cells containing North, and "<>" for cells that aren’t empty.
To use a variable or a cell value, join the operator to it with &: WorksheetFunction.CountIf(Range("A2:A11"), ">=" & Range("D1").Value). Writing ">=limit" inside the quotes compares against the word “limit” and finds nothing in a column of numbers, without an error.
For more than one condition, use CountIfs with pairs of range and criteria: WorksheetFunction.CountIfs(Range("A2:A11"), ">5000", Range("B2:B11"), "East"). Every range must be the same size.
Numbers stored as text aren’t counted by a numeric condition such as ">5000", so a count that looks too low usually means imported text. Convert the column with Data → Text to Columns → Finish before running the macro.
If you want to use the COUNTIF function in a VBA code, you need to use the WorksheetFunction property, which allows you to access all the Excel functions within a VBA code.
This tutorial will teach us to write and use COUNTIF in a code. For this, we have a few values in column A, and we need to count the values above 5000.
Write a VBA Code to use COUNTIF
Once you open the Visual Basic Editor from the developer tab, open a new module and create a new macro.
- First, type “Application” and then enter a dot (.) to the list of methods and properties.
- Now, select “WorksheetFunction” from the list or type it using the keyboard.
- Again, type a dot (.) to get the list of the functions, and from there, select the COUNTIF.
- Next, enter the starting parentheses to start the function. In the function’s first argument, specify the range from where you count the values with COUNTIF.
- From here, in the second argument, use a greater than operator using double quotation marks and enter 5000 to use as criteria.
- Last, you need to specify a cell to get the count in a cell.
Sub COUNTIF_VBA()
Range("B1") = Application.WorksheetFunction.CountIf(Range("A2:A11"), ">" & 5000)
End Sub
The above example shows that when you run the code, it returns the count in cell B1.
Important Note
When you use an Excel worksheet function in a VBA code using WorksheetFunction, it won’t show you the name of the arguments. So, you need to know the argument name before you write a code for the function.
In the above example, you can see that the names of the arguments are not there, but if you use COUNTIF in a worksheet, it shows you the names of the arguments.
If you want to get the result of COUNTIF in a message box, you can use the code below:
Sub COUNTIF_VBA()
MsgBox Application.WorksheetFunction.CountIf(Range("A2:A11"), ">" & 5000)
End Sub
Written by
Puneet Gogia
Microsoft MVP — Excel · Founder, Excel Champs
Twelve years of teaching Excel, and a decade before that using it as a data analyst. Every tutorial here is written from a file I actually built.