Excel Macro (VBA): How to use the CountIfs function in VBA!

Introduction.

Hello everyone! In this issue, I would like to explain "CountIfs in VBA", which many of you have asked me about, as clearly as possible.

Let me start my story with a "CountIfs function thing" that I have experienced. Actually, the other day, I received a consultation from one of our sales department members who said, "I created a macro using CountIfs, but it was running too slow...". Because he had put CountIfs directly into the cells, every time he executed another macro, all the rows were recalculated, and it was making things ridiculously slow.

Glossary: CountIfs
This is one of the Excel functions that counts the number of cells that match multiple conditions. It is useful when aggregating data with multiple conditions, for example, "sales of the sales department in Tokyo".

Basics of using COUNTIFS in VBA

Basics of Application.WorksheetFunction

First, let's look at some actual Excel data. Suppose we have the following sales data

The basic way to write COUNTIFS in VBA for this data is as follows:

'Basic Writing
Dim result As Long
result = Application.WorksheetFunction.CountIfs(Range("B:B"), "Tokyo")
'→ Count the number of "Tokyo" cases from column B (region)

Basic Writing of COUNTIFS

Let's look at this step-by-step using actual data.

1. use under simple conditions

For example, if you want to know the number of sales in Tokyo:

Sub Tokyo Count()
    Column 'B(at sentence-end, falling tone) indicates a confident conclusionRegion counts data for Tokyo
    Dim result As Long
    result = Application.WorksheetFunction.CountIfs( _
        Range("B:B"),. "Tokyo." _
    )
    Debug.Print result  ' Output results for confirmation
End Sub

When I run this code

It is displayed like this.
The number "4" was displayed because there are 4 Tokyo in "Region" in column B.

Tips: Immediate Window
Debug.Print allows you to display the results in the Immediate window (the window at the bottom of the VBE). This is useful to check if the result is correct.
For more information.How to display the Immediate Window Excel(Excel) Macro(VBA)Also check out the

2. use in a numerical range

If you want to know the number of sales over 10,000 yen:

Sub Number of sales over 10000 yen()
    'Counting data with sales of 10,000 yen or more
    Dim result As Long
    result = Application.WorksheetFunction.CountIfs( _
        Range("E:E"),. ">=10000" _
    )
    Debug.Print result
End Sub

When I run this code

was displayed.
The number "5" was displayed because there are 5 cells in column E, "Sales Amount," that have a value of 10,000 yen or more.

3. combination of multiple conditions

If you want to know the number of cases in Tokyo with sales of 10,000 yen or more:

Sub Sales over 10000 yen in Tokyo()
    'Counting data for sales over 10,000 yen in Tokyo.
    Dim result As Long
    result = Application.WorksheetFunction.CountIfs( _
        Range("B:B"),. "Tokyo.", _
        Range("E:E"), _ ">=10000" _
    )
    Debug.Print result
End Sub

When I run this code

was displayed.
The number "2" is displayed because there are two rows where the "Region" in column B is Tokyo and the "Sales Amount" in column E is 10,000 yen or more.

In this way, you can get a more concrete picture of how to use COUNTIFS if you look at it with actual data.

Practice: Learn through concrete examples of use

Example of use in sales data

Here is an example of how I actually handled this in the sales department. I needed a daily sales tally to cross-tabulate "by region" and "by product category.

sample data

Implementation example 1: Region x Category cross tabulation

Suppose you have a cell like this that you want to display the number of times stationery was sold in Tokyo.

Sub Crosstabulation()
    Dim ws As Worksheet
    Set ws = ThisWorkbook.Worksheets("Sales Data")
    'Number of stationery sales in Tokyo
    Dim tokyoStationeryCount As Long
    Dim tokyoStationeryCount As Long = Application.WorksheetFunction.CountIfs( _
        ws.Range("B:B"),. "Tokyo.", _
        ws.Range("C:C"), _ "Stationery." _
    )
    ws.Range("I3").Value = tokyoStationeryCount
End Sub

When I run this code

Thus, the I3 cell now shows the number of stationery items sold in Tokyo, "2".

In the case of the sample data, two cases (notebook A4 and eraser) were counted.

A little tip: ThisWorkbook means "this book". If you want to refer to data in another book, use Workbooks("book name").

Common mistakes and solutions

Here, let me tell you an example of a previous mistake I made. At first I wrote the following code:

For Each cell In Range("B:B")
    If cell.Value = "Tokyo" Then
        If cell.Offset(0, 1).Value = "Stationery" Then
            Count = Count + 1
        End If
    End If
Next cell

This code gives the same result (2 cases), but with a sheet with a million rows, it took several minutes to execute because it checks all the rows.... I had a bitter experience with this code at first too (sweat)

There are two problems with this code:

1. Loops through all rows, so if there is a lot of data, it becomes extremely slow.
2. Blank lines are also checked.

Therefore, we made the following improvements:

Dim ws As Worksheet
    Set ws = ThisWorkbook.Worksheets("Sales Data")
Dim lastRow As Long
    lastRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row

Dim result As Long
result = Application.WorksheetFunction.CountIfs( _
    ws.Range("B2:B" & lastRow), "Tokyo", _
    ws.Range("C2:C" & lastRow), "Stationery" _
)

What is 🔍 lastRow?
This method is used to get the last row of data. In the above example, we find "the first row with data from the bottom of column A to the top.

This has reduced the execution time from minutes to seconds! Since CountIfs is executed only for the range where data is available, there is no more wasted processing!

Compare, for example, when tested with 100,000 rows of data:

• Not good example: about 3 minutes 30 seconds
• Improved version: about 2 sec. The difference was astonishing.

💡 Performance Tips
Especially when dealing with large amounts of data, it is an ironclad rule to "process only the necessary range of data. I didn't realize it at first, but many users have told me with delight that "It runs faster now! I didn't realize it at first, but this improvement has made many users happy.

Example of use in human resources data

Next, I would like to share a real-life example from the Human Resources Department. We had to total up the number of A's by department and the number of A's who had been with the company for 5 years or more.

sample data

Implementation Example 2: Counting the number of rated A persons by department

Sub Personnel Summary()
    Dim ws As Worksheet
    Set ws = ThisWorkbook.Worksheets("Personnel Data")
    
    'Number of A's rated in the Sales Department
    Dim salesTopCount As Long
    salesTopCount = Application.WorksheetFunction.CountIfs( _
        ws.Range("C:C"), "Sales", _
        ws.Range("F:F"), "A" _
    )
    
    Display 'Results
    Debug.Print "Sales Department Evaluation A Number of people: " & salesTopCount & "Name"
End Sub

If you run this code, you will see that

The Sales Department now has four employees with an A rating.

You can see the status of each department at a glance like this.

Implementation example 3: Advanced aggregation combining conditions

With the same data,More than 5 years of continuous serviceindicates certainty, emphasis, etc.Rating is ACount the number of people in the

Sub A() with more than 5 years of service
    Dim ws As Worksheet
    Set ws = ThisWorkbook.Worksheets("Personnel Data")
    'Number of employees with at least 5 years of service and evaluation A
    Dim experiencedTopCount As Long
    experiencedTopCount = Application.WorksheetFunction.CountIfs( _
        ws.Range("E:E"),. ">=5", _
        ws.Range("F:F"), _ "A". _
    )
    Display 'Results
    Debug.Print "Number of Evaluation A's with more than 5 years of service: " & experiencedTopCount & "Names"
End Sub

If you run this one

We found that there are three employees who have been with the company for more than five years and have an A rating.

Tips on numerical conditions
In numerical condition specification,">=" (greater than or equal to), "" (greater than), "" (greater than or equal to), "<" (less than or equal to)can be used. At first I was also worried about how to write ">= number" to extract more than 5 years, but I found that it is easy to specify by writing ">= number"!

Implementation Example 4: Use in more complex conditions

sales departmentindicates certainty, emphasis, etc.Over 30 years old(at sentence-end, falling tone) indicates a confident conclusionRating ALet's count the number of employees with three conditions: employees of the

Sub Sales Department and over 30 years old and Rating A()
    Dim ws As Worksheet
    Set ws = ThisWorkbook.Worksheets("Personnel Data")
    'Sales Department and over 30 years old and rated A
    Dim seniorTopCount As Long
    seniorTopCount = Application.WorksheetFunction.CountIfs( _
        ws.Range("C:C"),. "Sales.", _
        ws.Range("D:D"), _ ">=30", _
        ws.Range("F:F"), _ "A". _
    )
    Display 'Results
    Debug.Print "Number of A rated 30+ in sales department: " & seniorTopCount & "Names"
End Sub

When I run this code

It was displayed like this.

The three conditions could be combined and still be written as simple as this.

Example of use in inventory control

In inventory control, it is important to manage product order points and check inventory status by warehouse. Here is a case study that I previously handled for a stationery manufacturer.

sample data

Implementation Example 5: Counting items at the point of order (less than 50 items in stock)

Sub Less than 50 in stock()
    Dim ws As Worksheet
    Set ws = ThisWorkbook.Worksheets("Inventory Data")
    
    'Number of items less than 50 in stock
    Dim lowStockCount As Long
    lowStockCount = Application.WorksheetFunction.CountIfs( _
        ws.Range("D:D"),. "<=50" _
    )
    
    Display 'Results
    Debug.Print "Number of items that need to be ordered: " & lowStockCount & "pieces"
End Sub

Execution.

We have found that there are 4 products with less than 50 items in stock. Now we can instantly figure out which items need to be ordered.

Implementation Example 6: Number of specific category products by warehouse

TokyoWarehousestationeryCount the number of products in a category.

Sub Number of Stationery Category Products in Tokyo Warehouse()
    Dim ws As Worksheet
    Set ws = ThisWorkbook.Worksheets("Inventory Data")
    
    'Number of products in the stationery category at Tokyo Warehouse
    Dim tokyoStationeryCount As Long
    tokyoStationeryCount = Application.WorksheetFunction.CountIfs( _
        ws.Range("C:C"), "stationery", _
        ws.Range("F:F"), "Tokyo" _
    )

    Debug.Print "Number of stationery items in the Tokyo warehouse: " & tokyoStationeryCount & "points"
End Sub

If you run this code, you will see that

We see that there are three stationery items in the Tokyo warehouse. It is easy to see the inventory composition by warehouse!

Implementation Example 7: Inventory by price range

1,000 yen or moreandMore than 20 in stockLet's look at how to specify a range, the number of products in the

Sub Number of items with an amount of at least 1000 yen and at least 20 items in stock()
    Dim ws As Worksheet
    Set ws = ThisWorkbook.Worksheets("Inventory Data")
    
    'Number of items in stock 20 or more for $1,000 or more
    Dim highValueStockCount As Long
    highValueStockCount = Application.WorksheetFunction.CountIfs( _
        ws.Range("E:E"),. ">=1000", _
        ws.Range("D:D"), _ ">=20" _
    )

    Debug.Print "Number of items over $1000 and over 20 in stock: " & highValueStockCount & "points"
End Sub

When I run this code

We have identified two items that are over $1,000 and have at least 20 items in stock. This information will help us keep track of inventory amounts.

💡 Practical Application Points
If you perform this type of tally on a regular basis, it is recommended that you output the tally results to a separate sheet and graph them. Since I adopted this method, reporting inventory status has become easier. It has greatly sped up the preparation of reporting materials to management.

FAQ

Here is a summary of the most frequently asked questions we receive.

Q1: How do I dynamically change search criteria?

A: A You can set conditions using variables. It looks like this:

Sub DynamicCountIfs()
    'Set conditions by variable
    Dim. region As String
    Dim minAmount As Long
    
    region = Range("A1")  'Region (AI cell is populated with "Tokyo")
    minAmount = 10000  ' Minimum Amount
    
    Counting with 'variables
    Dim result As Long
    result = Application.WorksheetFunction.CountIfs( _
        Range("B:B"),. region, _
        Range("E:E"), ">=" & minAmount _
    )

    Debug.Print region & "of" & minAmount & "Number of cases over ³: " & result
End Sub

When executed, it will display "Number of items in Tokyo over 10000 yen: X items". If you want to change the condition, simply change the value of the variable (B1 cell)!

Q2: What precautions should I take when processing large amounts of data?

A: A The following three points are important

1. Minimize the scope of processing to the minimum necessary

Dim lastRow As Long
lastRow = Cells(Rows.Count, "A").End(xlUp).Row
Range ("A2:A" & lastRow) ' Specify only the required range

2. Utilize auto-filters

'Use auto-filter to extract only the necessary data before counting
With ActiveSheet
    AutoFilterMode = False  Clear 'filter
    .Range("A1").AutoFilter  Set 'Filter
    .Range("A1").AutoFilter Field:=2, Criteria1:="Tokyo"  'Show only Tokyo data
End With

Conclusion

How was it? CountIfs in VBA may seem a bit daunting at first, but once you get the basic patterns down, it is a very useful feature.

The key points to remember in particular are:

1. Specifying the range is the minimum necessary.
2. Don't forget to handle errors.
3. Be careful with date and number formats.

Please try simple conditions first, and then gradually try more complex conditions.

💡 Next step: once you have mastered CountIfs, try "Speeding up with Arrays" and "Aggregating with Dictionary" next. I explain them in detail in a separate article!