In Excel VBA, count cells that meet one condition with Application.WorksheetFunction.CountIf(range, criteria). For example, if A2:A100 contains status values, VBA can count cells equal to Approved and place the result in another cell; COUNTIF is case-insensitive and also accepts criteria such as >10 or text wildcards.
What COUNTIF Does in VBA
COUNTIF is an Excel worksheet function that counts cells in a range according to one criterion. VBA lets you call it from a macro, procedure, or user-defined function, so you can calculate a count as part of a larger workflow instead of entering a worksheet formula manually. It is useful for summaries, checks, and decisions such as whether a category appears in a list.
The VBA call has two inputs: the range to inspect and the criterion to apply. The range is typically an Excel Range object, while the criterion may be a string, number, cell value, or expression. A single COUNTIF call applies one criterion to the range. To count using multiple conditions, use a different approach, such as COUNTIFS or a loop that checks each condition.
In a worksheet, COUNTIF is written as a formula beginning with an equals sign. In VBA, you call the function by name and pass its arguments in parentheses. The result is a number, which your code can store, display, or use in an If statement.
Basic VBA Syntax and a Working Macro
Suppose Sheet1 has status values in A2:A100 and you want to count the cells containing Approved. Put this procedure in a standard VBA module. It writes the count to C2 on the same sheet.
Option Explicit
Sub CountApprovedStatuses()
Dim ws As Worksheet
Dim dataRange As Range
Dim criteria As String
Dim result As Long
' Identify the worksheet and the cells to count.
Set ws = ThisWorkbook.Worksheets("Sheet1")
Set dataRange = ws.Range("A2:A100")
criteria = "Approved"
' Count matching cells and place the result in C2.
result = Application.WorksheetFunction.CountIf(dataRange, criteria)
ws.Range("C2").Value = result
End Sub
Before running a macro, save a copy of the workbook. Macro actions cannot be reversed with Ctrl+Z, so a backup gives you a way to recover if a procedure changes the wrong cells. In Excel for Microsoft 365 on Windows, open the Visual Basic Editor with Alt+F11, choose Insert > Module, paste the procedure, and run it from the editor or from Excel’s macro list. On Mac, the Visual Basic Editor shortcut is Option+F11 (on some keyboards, you may also need Fn); the editor and module workflow are similar. Excel for the web can open a workbook containing VBA, but it cannot create, edit, or run VBA macros.
Criteria: Text, Numbers, Comparisons, and Wildcards
For an exact text match, pass the desired text as a string. COUNTIF does not distinguish uppercase from lowercase, so Approved and APPROVED match the same cells. If the criterion is stored in a worksheet cell, pass that cell’s value rather than typing a fixed string. This makes a macro easier to reuse when the user can change the selection.
To count numbers above, below, or equal to a threshold, the criterion must include a comparison operator. In a worksheet formula, when values are in B2:B20 and the threshold is 10, the formula is =COUNTIF(B2:B20,">10"). It counts numbers strictly greater than 10, not values equal to 10. In VBA, build the criterion from an operator and a value, for example ">" & 10. The ampersand joins the operator text and number into the criterion string.
When values are in B2:B20 and the comparison threshold is in D1, the worksheet formula is =COUNTIF(B2:B20,">"&D1). In VBA, the matching idea is ">" & ws.Range("D1").Value. This is a common point of confusion: putting a cell reference inside quoted text makes it literal text, not a reference to that cell. Concatenation is what combines the operator with the cell’s current value.
COUNTIF supports two wildcard characters for text criteria:
*matches any sequence of characters, including no characters. The criterion"West*"matches text beginning with West.?matches any single character. The criterion"A?3"matches values such as AB3 or AX3.- To search for a literal asterisk, question mark, or tilde, prefix it with a tilde. For instance, the criterion
"~*"looks for an asterisk character.
Wildcards apply to text matching; they are not a general pattern-matching language. If the criterion includes a cell value containing a wildcard character that should be treated literally, escape that character with a tilde before using it. If you need case-sensitive matching or complex text rules, a VBA loop with explicit comparisons may be more suitable.
Using Worksheet Values and Dynamic Ranges
A macro is more flexible when it reads its criterion and data range from cells instead of relying on fixed values. For instance, a user might type a status into D1 and expect the count in E1. With status data in A2:A100 on Sheet1, the VBA expression can use ws.Range("D1").Value as the second argument. This means the same macro can count different statuses without editing its code.
For data that grows, a fixed range such as A2:A100 eventually misses new rows. One simple option is to determine the final used row and build a range from the first data row to that point. That approach needs care if the column can contain gaps: using the last occupied cell in the column is not necessarily the same as finding the last row of a complete dataset. If the source is an Excel Table, referencing its data column is often clearer because the table expands as new records are added. Check the table and column names in the workbook before using them in code.
Also qualify worksheet and workbook references. A reference such as Range("A2:A100") without a worksheet qualifier depends on which sheet is active. A macro can therefore count the wrong cells if the user switches sheets. Using ws.Range(...), with ws explicitly set to the intended worksheet, makes the target unambiguous. Use ThisWorkbook when the code should act on the workbook containing the macro; ActiveWorkbook instead refers to whichever workbook is currently active.
Returning the Count to a Cell or a Variable
The result does not have to go directly to a worksheet cell. Store it in a variable when later code needs to act on the count. For example, a macro could show a message if no rows have a particular status, or skip an export when there are no matching records. An If statement can test the count before the macro continues.
For a worksheet formula rather than a VBA calculation, with status values in A2:A100 and the desired status entered in D1, use =COUNTIF(A2:A100,D1) in E1. This formula updates when the range or criterion changes, without running a macro. By contrast, VBA can calculate on demand and then write a fixed result to a cell. The right choice depends on whether the count should recalculate as worksheet data changes or be produced as part of an automated task.
In VBA, Application.WorksheetFunction.CountIf returns a value that can be assigned to a numeric variable. This form is convenient for normal inputs, but worksheet-function errors can become VBA runtime errors. If the data or criterion may be invalid, add appropriate error handling or validate inputs before the call. An alternative is Application.CountIf, which returns a Variant and can represent an Excel error value; code using that form should check for an error before treating the result as a number. Neither form removes the need to confirm that the range and criterion are what the procedure expects.
COUNTIF, COUNTIFS, and a VBA Loop
Choose COUNTIF when you have one condition. If you need to count rows where a department is Sales and the corresponding amount exceeds 1000, COUNTIFS is designed to apply multiple criteria ranges and criteria together. Its worksheet syntax has pairs of range and criterion arguments. VBA can call COUNTIFS in the same general way as COUNTIF, but each range must align with the others so corresponding rows are evaluated together.
A loop is an alternative when the rules are too specialized for COUNTIF. For example, a loop can make a case-sensitive text comparison, apply several tests with custom logic, or handle values in a way that is awkward to express as a criterion string. The trade-off is that code must inspect each cell and maintain the counting logic itself. For a straightforward one-condition count, COUNTIF is usually shorter and easier to review. For a very large dataset or repeated calculations, performance can depend on how often the macro calls worksheet functions and how much work it does in each iteration; avoid recalculating the same count unnecessarily.
Common Problems and How to Fix Them
- The count is zero unexpectedly: Check spelling, leading or trailing spaces, and whether the criterion is being read from the intended cell. COUNTIF ignores letter case, but it does not generally treat extra spaces as irrelevant.
- A comparison does not work: Join the operator and threshold, such as
">" & 10. Do not pass a cell reference as part of a quoted literal string. - The macro counts the wrong sheet: Qualify the range with a worksheet variable instead of relying on the active sheet. Confirm whether the target workbook should be ThisWorkbook or ActiveWorkbook.
- The count stops including new records: Replace a short fixed range with a suitable dynamic range or a table column, and verify how blank rows are handled.
- A date criterion returns an unexpected result: Excel stores dates as serial numbers, while their displayed format can vary. Build date comparisons from actual date values rather than ambiguous date text, and check the workbook’s regional date interpretation.
- The code stops with an error: Check that the worksheet exists, the range reference is valid, and the criterion is acceptable. If input can be missing or malformed, validate it and handle possible errors deliberately.
COUNTIF criteria can be subtle around empty cells. A criterion of an empty string can count blank cells, and cells containing formulas that display an empty string may also affect results. If the distinction between truly empty cells and formulas returning empty text matters, test the actual workbook data and consider a loop that checks the cell’s formula or value explicitly.
Frequently Asked Questions
How do I use COUNTIF in Excel VBA?
Call Application.WorksheetFunction.CountIf with a range and a criterion. Assign the returned count to a variable or write it to a worksheet cell. Qualify the range with the intended worksheet so the macro does not depend on the active sheet.
Is COUNTIF case-sensitive in VBA?
No. COUNTIF is not case-sensitive, whether called from a worksheet formula or VBA. If uppercase and lowercase must be treated differently, use a VBA loop and a case-sensitive comparison approach rather than COUNTIF.
Can COUNTIF check more than one condition?
One COUNTIF call applies one condition. For multiple conditions, use COUNTIFS when the rules fit its range-and-criteria model, or write a loop for custom logic.
How do I use a greater-than condition?
Construct a criterion by joining the comparison operator and the threshold, such as ">" & 10. This counts values strictly greater than 10; use a different operator if the boundary should be included.
Can I run a VBA COUNTIF macro in Excel for the web?
No. Excel for the web can store and open a macro-enabled workbook, but VBA macros run in the desktop Excel application. Use Excel for Microsoft 365 on Windows or Mac to run the procedure.
Should I use a worksheet formula or VBA?
Use the worksheet formula when you want a visible count that updates with cell changes. Use VBA when counting is one step in an automated process, such as producing a report or controlling what a macro does next.
Related
- Excel Timesheet Format: Build a Reliable Weekly Sheet
- Excel Timesheet for Multiple Employees: Setup and Tips
- Excel Invoice Bill Format: Build a Useful Template
Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.
More in VBA & macros
- VBA Array AutoFilter: Filter Data in Memory or ExcelExcel’s VBA AutoFilter method filters a worksheet range or table; it does not filter a VBA array directly.…
- VBA MsgBox: How to Display Messages in ExcelIn Excel VBA, MsgBox displays a dialog box containing a message and optional buttons, and it can return…
- Access VBA Dictionary: Create and Use Scripting.DictionaryYes. In Access VBA, you can use the Scripting.Dictionary object to store pairs of keys and values, check…
- How to Copy and Paste Formulas with VBA in Excel SafelyTo copy a formula with VBA in Excel, copy its source cell and use PasteSpecial Paste:=xlPasteFormulas on the…
- MS Access VBA Dictionary: Setup, Examples, and TipsYes. Microsoft Access VBA can use the Scripting.Dictionary object to store key-value pairs, look up a value by…
- Excel VBA Filter Range: AutoFilter Examples and FixesTo filter a range with Excel VBA, use the range’s AutoFilter method and specify the field number and…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.