In Excel VBA, you can count cells that meet multiple criteria by using the WorksheetFunction.CountIfs method, which mirrors the worksheet function COUNTIFS. For example, WorksheetFunction.CountIfs(Range("A:A"), "North", Range("B:B"), ">100") counts rows where column A equals “North” and column B is greater than 100. This method is the direct VBA equivalent and works in all modern Excel versions, though you must handle errors when no matches are found.
Background: Why Use CountIfs in VBA?
Excel’s COUNTIFS worksheet function is a staple for conditional counting, but when you need to automate reports, dashboards, or data cleaning, you often want that count inside a VBA macro. Instead of writing a count to a cell and then reading it, VBA lets you compute the count directly in code, making your macros faster and more self-contained. The WorksheetFunction object exposes most worksheet functions to VBA, and CountIfs is one of them. It was introduced in Excel 2007, so it is available in all versions you are likely to use today.
How CountIfs Works in VBA
The syntax for WorksheetFunction.CountIfs is:
WorksheetFunction.CountIfs(CriteriaRange1, Criteria1, [CriteriaRange2, Criteria2], ...)
You can pass up to 127 range/criteria pairs. Each range must be the same size and shape, and criteria can be numbers, strings, or expressions. In VBA, you often build criteria strings dynamically. For example, to count orders in region “West” with amount over 500, assuming data in columns A and B:
Dim count As Long
count = WorksheetFunction.CountIfs(Range("A2:A100"), "West", Range("B2:B100"), ">500")
This returns the count directly. Note that VBA uses the same wildcard characters as the worksheet: asterisk (*) for any sequence, question mark (?) for a single character. So "A*" matches any text starting with A.
Choosing Between CountIfs, CountIf, and AutoFilter
Sometimes you have a choice. For a single criterion, WorksheetFunction.CountIf is simpler. For multiple criteria, CountIfs is the natural fit. But if your criteria are complex (e.g., OR logic, or based on calculated values), you might consider using AutoFilter and counting visible cells, or looping through a range. Here is a quick guide:
- Single criterion: Use
WorksheetFunction.CountIf. - Multiple AND criteria: Use
WorksheetFunction.CountIfs. - OR logic or dynamic ranges: Use
AutoFilterwithSubtotalor loop withIf. - Very large datasets (100k+ rows): Looping can be slow; prefer
CountIfsor a dictionary approach if you need unique combinations.
Common Problems and How to Fix Them
The most common issue is a runtime error when no cells match. WorksheetFunction.CountIfs throws a Run-time error '1004' if the result would be zero. To avoid this, wrap the call in error handling or use On Error Resume Next. For example:
Dim count As Long
On Error Resume Next
count = WorksheetFunction.CountIfs(Range("A:A"), "X", Range("B:B"), 1)
If count = 0 Then count = 0 ' or handle differently
On Error GoTo 0
Another issue is mismatched range sizes. If you pass Range("A1:A10") and Range("B1:B5"), you get an error. Always ensure ranges have the same number of rows and columns. Also, remember that criteria strings are case-insensitive by default, just like in the worksheet.
If you need to count based on a cell value (e.g., a criterion stored in a cell), you can concatenate: "" & Range("D1").Value. For numeric comparisons, build the string with ">" & Range("D2").Value.
Comparing CountIfs with Other Methods
Let’s compare three approaches for counting rows where column A = “Yes” and column B > 10:
- WorksheetFunction.CountIfs – Fast, concise, but throws on zero.
- AutoFilter – More flexible (can filter on many conditions), but modifies the sheet and requires counting visible cells with
SubtotalorSpecialCells. - Loop – Slow for large data, but allows any logic (e.g., OR, complex conditions).
For most automation, CountIfs is the best balance of speed and simplicity. If you need to avoid the zero error, you can also use Application.CountIfs (without WorksheetFunction) which returns a Variant and does not throw; it returns an error value instead, which you can test with IsError.
Practical Example: Counting with Multiple Criteria
Suppose you have a sales report with headers in row 1: Region (A), Product (B), Sales (C). You want to count rows where Region = “East” and Sales > 1000. In VBA:
Sub CountEastHighSales()
Dim lastRow As Long
Dim count As Long
lastRow = Cells(Rows.Count, "A").End(xlUp).Row
count = WorksheetFunction.CountIfs(Range("A2:A" & lastRow), "East", _
Range("C2:C" & lastRow), ">1000")
MsgBox "Count: " & count
End Sub
This macro finds the last used row in column A, then counts matching rows. Notice the use of _ to continue the line. Always save a copy of your workbook before running any macro, as macros cannot be undone with Ctrl+Z.
Frequently Asked Questions
Can I use CountIfs with dates in VBA? Yes, but you must format the date criterion correctly. For example, to count dates after January 1, 2023, use ">" & DateSerial(2023, 1, 1) or ">" & Format(DateValue("1/1/2023"), "mm/dd/yyyy"). Be consistent with your system’s date format.
Why does CountIfs give a #VALUE! error when used in a cell? That’s a worksheet issue, not VBA. In a worksheet, COUNTIFS returns #VALUE! if the ranges are not the same size. In VBA, you get a runtime error. Check that all criteria ranges have identical dimensions.
How do I count with OR logic in VBA? CountIfs only does AND logic. For OR, you can sum multiple CountIfs calls (e.g., CountIfs(range, "A", range2, "X") + CountIfs(range, "B", range2, "X")) but be careful not to double-count rows that meet both conditions. Alternatively, use an array formula or a loop.
Is there a performance difference between CountIfs and looping? Yes. CountIfs is compiled and optimized, so it is much faster than a VBA loop for large ranges. For 100,000 rows, CountIfs runs in a fraction of a second, while a loop might take several seconds. Use CountIfs whenever possible.
Can I use CountIfs on a non-contiguous range? No, COUNTIFS requires contiguous ranges. If you need to count across multiple separate ranges, you’ll need to sum multiple CountIfs calls or use a different approach like a Union range with a loop.
Alternatives to CountIfs in VBA
If CountIfs doesn’t fit your needs, consider these alternatives:
- SUMPRODUCT:
WorksheetFunction.SumProductcan handle multiple criteria and even OR logic, but it is an array formula and can be slower on huge data. - AutoFilter + Subtotal: Filter the data and then use
Subtotal(103, range)to count visible cells. This is useful when you also need to display the filtered result. - Dictionary object: If you need to count unique combinations of criteria, a dictionary can be efficient.
- Power Query: For complex data transformation, consider loading data into Power Query and using its grouping features, but that is outside VBA.
Each method has trade-offs. For simple multiple-criteria counts, CountIfs is usually the cleanest and fastest. For more complex logic, you may need to combine methods or loop.
Related
- Excel Sum Only Numbers Ignore Text in Cell?
- Excel Sum Showing Zero? Fixes That Work
- Excel Spreadsheet Zoom Lock: How to Freeze Zoom Level
Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.
More in VBA & macros
- Excel VBA: How to Copy a Module to Another WorkbookTo copy an Excel VBA module to another workbook, open both files in desktop Excel, press Alt+F11, and…
- Excel VBA Object Required? Causes and FixesIn Excel VBA, “Object required” is usually run-time error 424: VBA expected an object reference but received a…
- Excel VBA: Add a Worksheet With a Specific Name SafelyTo add a worksheet with a specific name in Excel VBA, add the sheet to the intended workbook…
- Excel VBA Visible Worksheet Property: How to Set ItIn Excel VBA, a worksheet’s visibility is controlled by its Visible property. Set it to xlSheetVisible to show…
- VBA Macro Excel Workbook: Enable Editing on Open SafelyNo: a VBA macro cannot safely or legitimately click Excel’s Enable Editing button in Protected View. To edit…
- Excel VBA: Add a New Sheet at the End and Name It SafelyTo add a worksheet after the workbook’s last tab and name it in Excel VBA, use Worksheets.Add with…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.