VBA COUNTIFS

What is VBA COUNTIFS?

The number of cells in a range that satisfies several requirements can be counted using the VBA COUNTIFS function. When working with huge datasets or needing to filter data based on many conditions, this function comes in handy.

Let us examine an illustration. Suppose we have some Excel data, and we wish to find the number of cells that contain values. However, some of the cells are blank. It can be accomplished with ease by utilizing the VBA COUNTIFS function.

In this example, we have a subroutine named “Example_CountIFS_NotBlank” that counts the number of non-blank cells in the range A1:A10 on Sheet1.

Here, we use the VBA COUNTIFS function with only one criterion, “<>,” which signifies “not blank.” The result is stored in a variable, countResult, and finally, a message box displays the count of non-blank cells.

Key Takeaways
• The COUNTIFS function is used to count cells that meet multiple criteria using VBA code.
• To count the number of occurrences, in each case specify the range, followed by its required criterion in the COUNTIFS function.
• VBA COUNTIFS Multiple Criteria feature enables counting cells based on multiple criteria simultaneously, providing flexibility in data analysis tasks requiring evaluation against multiple conditions.
•  The VBA COUNTIFS OR Criteria feature enables users to utilize the “OR” operator to specify several conditions, enabling inclusive counting based on a variety of criteria.

Syntax of VBA COUNTIFS

The syntax for the VBA COUNTIFS function is:

Application.WorksheetFunction.CountIfs(range1, criteria1, range2, criteria2, …)

Where:

• range1, range2, … are the ranges to evaluate.
• criteria1, criteria2, … are the conditions that must be met.

How to use VBA COUNTIFS?

Step 1: Open Excel and press ALT + F11 to open the VBA editor.

Step 2: Insert a new module by clicking on “Insert” in the menu and selecting “Module.”

Step 3: In the module window, write a subroutine to house the COUNTIFS operation.

Step 4: Declare variables to store the result of the count and any ranges you’ll be evaluating. Use the Dim keyword followed by the variable name and its data type (e.g., As Long for integers).

Step 5: Set the ranges to be evaluated using the Set keyword followed by the range variable name and the specific range using “Range(“A1:A10”)” or other appropriate range selection methods.

Step 6: Use the “Application.WorksheetFunction.CountIfs” method to perform the count operation. Provide the ranges and criteria within parentheses. Separate each range and criterion with a comma.

Step 7: Assign the result of the count operation to the variable declared earlier to store the count result.

Step 8: You can display the count result using a message box in VBA or output it to a cell in the worksheet for further analysis.

Step 9: Execute the macro either by pressing F5 or clicking Run from the menu.

Examples

Let us look at some interesting examples to understand more about the COUNTIFS function.

Example #1 – COUNTIFS on Multiple Range Objects

Suppose you have two ranges of data, and you want to count the number of cells that meet specific criteria in both ranges. You can use the VBA COUNTIFS to achieve this.

Step 1: In the new module, start by declaring a subroutine named “Example1_CountIFS_MultipleRanges.”

Step 2: In this step, we declare the following three variables:

countResult: Stores the result of the count.

rng1 and rng2: Represent the ranges to be evaluated.

Step 3: Here, we set the rng1 and rng2 variables to represent specific ranges on the worksheet named Sheet2.

Step 4: In this line, the VBA COUNTIFS function is used to count the cells in rng1 that are greater than ten and in rng2 that are less than 20.

Next, the result is stored in the countResult variable.

Step 5: Finally, a message box is displayed showing the count result.

Step 6: Now, save the macro and click on run.

When the code is executed, it will count the number of cells in the ranges A1:A10 and B1:B10 that meet the specified criteria (greater than 10 and less than 20).

The result will be displayed in a message box.

Here is the full code:

Sub Example1_CountIFS_MultipleRanges()
Dim countResult As Long
Dim rng1 As Range
Dim rng2 As Range
Set rng1 = Sheet2.Range(“A1:A10”)
Set rng2 = Sheet2.Range(“B1:B10”)
countResult = Application.WorksheetFunction.CountIfs(rng1, “>10”, rng2, “<20”)
MsgBox “Count: ” & countResult
End Sub

Example #2 – COUNTIFS With Text Criteria:

We can also use VBA COUNTIFS to count cells based on text criteria. For example, if you have a list of names and you want to count how many times a particular name appears (in this example, John), you can use the VBA COUNTIFS.

Step 1: First, let’s declare the new subroutine named “Example2_CountIFS_TextCriteria.”

Step 2: Just like the previous example, here, we declare the two variables.

countResult: Stores the result of the count.

nameRange: Represents the range containing names.

Step 3: This line sets the “nameRange” variable to represent a specific range on the worksheet named Sheet3.

Step 4: Now, the VBA CountIfs function is used to count the occurrences of the name “John” in the “nameRange” variable. The result is stored in the countResult variable.

Step 5: Next, we use the message box feature to display our result.

Step 6: When executed, the code will count the number of occurrences of the name “John” in the range A1:A10 on Sheet 3 and display the result in a message box.

Here is the full code:

Sub Example2_CountIFS_TextCriteria()
Dim countResult As Long
Dim nameRange As Range
Set nameRange = Sheet3.Range(“A1:A10”)
countResult = Application.WorksheetFunction.CountIfs(nameRange, “John”)
MsgBox “Count: ” & countResult
End Sub

Example #3 – COUNTIFS With Dates

Suppose you have a list of dates, and you want to count how many fall within a specific range; you can use the VBA COUNTIFS to achieve this.

Step 1: This line declares the start of a subroutine named Example3_CountIFS_Dates.

Step 2: Just like the previous two examples, we declare the two variables: countResult and dateRange.

Step 3: This line sets the “dateRange” variable to represent a specific range on the worksheet named Sheet4.

Step 4: Here, the VBA COUNTIFS function is used to count the dates within “dateRange” that fall between January 1, 2023, and December 31, 2023. The result is stored in the countResult variable.

Step 5: A message box is displayed showing the count result.

Step 6: When executed, the code will count the number of dates falling within the specified range in the range A1:A10 on Sheet1 and display the result in a message box.

Here is the full code:

Sub Example3_CountIFS_Dates()
Dim countResult As Long
Dim dateRange As Range
Set dateRange = Sheet4.Range(“A1:A10”)
countResult = Application.WorksheetFunction.CountIfs(dateRange, “>=” & DateSerial(2023, 1, 1), dateRange, “<=” & DateSerial(2023, 12, 31))
MsgBox “Count: ” & countResult
End Sub

Important Things to Note

• You can simultaneously specify many criteria with the VBA COUNTIFS. Only cells that satisfy every condition are tallied; each criterion is assessed separately.
• Make sure that the ranges in VBA COUNTIFS are correctly aligned and cover the same number of rows when utilizing multiple ranges.
• Ensure that the cell types being assessed and the data types of the criteria corresponds. For instance, comparisons between text criteria and text data, date criteria and date data, and numeric criteria and numeric data should be made.
• VBA COUNTIFS Between Dates allows counting cells that fall within a specified date range, facilitating analysis of time-based data sets efficiently in VBA.
• By using the VBA COUNTIFS Not Blank feature, users can count cells that are not blank, enabling efficient handling of datasets with missing or incomplete information using COUNTIFS in VBA.

Frequently Asked Questions (FAQs)

1. How is the COUNTIFS function different from the COUNTIF function?

You can count the number of cells in a range that satisfy a single given criterion by using the VBA CountIf function. However, you can expand this functionality by counting cells depending on numerous criteria at once with the help of the COUNTIFS function.

This implies that you can set several criteria, and only the cells that satisfy them all will be tallied. For instance, you can use CountIfs to count the number of cells in a range that are less than 20 and higher than 10, which is not possible with VBA COUNTIF.

2. Can I use the COUNTIFS function with dynamic ranges?

Yes, you may use dynamic ranges with the VBA COUNTIFS function. Dynamic ranges are those whose sizes are automatically modified in response to modifications in the data. You can run counts on changing datasets without having to manually change the range references in your code by defining your ranges using variables or dynamic range names.

3. How can I use wildcards with the CountIfs function?

To do partial matches or pattern matching, you can utilize wildcards like * and? with text criteria in the COUNTIFS method. For instance, “*apple*” counts the number of cells in the text that contain the word “apple” at any point. This makes it possible to count and search text values in a flexible way using patterns rather than exact matches.

4. How can I handle errors or empty cells when using the CountIfs function?

When using the COUNTIFS function, you can check for empty cells beforehand or utilize error-handling mechanisms to handle errors or empty cells. For example, you can use the IsEmpty method to see if a cell is empty, or the “IsError” function to see if a cell contains an error value.

You may make sure that your count operations are reliable and accurate even when the dataset contains mistakes or empty cells by including these kinds of checks in your code.