What is the VBA SUMIFS Function in Excel?
The VBA SUMIFS function in excel provides a flexible tool for adding data based on various criteria. By enabling the specification of multiple criteria, it expands the capabilities of the SUMIF function.
Let us look at an example. Suppose we have a dataset containing information about various items, including their quantities, costs, and total costs, and we want to compute the sum of total costs for items with costs exceeding 10. It can be done using the VBA SUMIFS Function.
In this VBA example, the Sub procedure named TestSumIfGreaterThan is defined. In this process, cell D10 is given the formula =SUMIFS(D2:D9, C2:C9, “>10”).
Under the assumption that the corresponding values in the range C2:C9 are more than 10, this formula determines the sum of the values in the range D2:D9.
Here, we showcase how to execute conditional summing operations in VBA, i.e., to sum numbers based on a condition of being more significant than a given value by utilizing the SUMIFS function.
When you run this code, it will apply the SUMIFS formula to cell D10, providing the sum of values meeting the specified criteria.
Syntax
The syntax for the SUMIFS function in VBA is:
Application.WorksheetFunction.SumIfs(sum_range, criteria_range1, criteria1, [criteria_range2], [criteria2], …)
Syntax | Description |
---|---|
sum_range | The range of cells that you want to sum. |
criteria_range1 | The first range of cells that you want to apply criteria to. It is where the first condition will be evaluated. |
criteria1 | The criteria that you want to apply to the first criteria_range1. It can be a number, text, expression, or cell reference. |
[criteria_range2] | Optional. Additional range of cells that you want to apply criteria to. |
[criteria2] | Optional. The criteria that you want to apply to the optional criteria_range2. It can be left blank if not needed. |
… | Additional pairs of criteria_range and criteria can be provided as needed to specify multiple conditions. |
Table of Contents
Key Takeaways
- The VBA SUMIFS function is a powerful tool for summing values based on multiple criteria in Excel VBA.
- It extends the functionality of the SUMIF function by allowing multiple conditions to be specified.
- Dynamic ranges, date criteria, string criteria with wildcards, and combining the VBA SUMIFS with other functions like VLOOKUP or SUM can enhance its utility in various scenarios.
- With the VBA SUMIFS, users have the flexibility to apply multiple criteria simultaneously, allowing for precise data selection and aggregation across different sheets.
How to Use VBA SUMIFS Function?
Using the VBA SUMIFS function in VBA involves the following steps:
- 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: Within the module, write the VBA code that utilizes the SUMIFS function. Define a Sub procedure where you’ll write your code.
- Step 4: Declare any variables you need for your calculation. This step is optional but recommended for better organization and readability.
- Step 5: Utilize the VBA SUMIFS function from the Application.WorksheetFunction object to perform the summing operation based on specified criteria.
Provide the necessary arguments to the SUMIFS function:
- Step 6: You can choose to display the result using message boxes (MsgBox), write it to a cell, or use it for further calculations within your VBA code.
- Step 7: Save and run the VBA code by pressing F5 in the VBA editor.
Examples
Example #1 – Less than Numeric Criteria
In this example, we have a dataset containing two columns. We want to calculate the sum of values in Column B where the corresponding values in Column A are less than three from this data set using the VBA SUMIFS function.
Step 1: In the new module, we start by defining a subroutine named Example1.
Step 2: We declare a variable named result as a Double data type to store the result of the calculation.
Step 3: We use the SUMIFS function from the “Application.WorksheetFunction” object to sum the values in column B based on the condition that the corresponding value in column A is less than 3.
Step 4: We display the result using a message box.
Step 5: Save the macro and click on run.
When the code is run, it calculates the sum of values in column B where the corresponding value in column A is less than three and displays the result in a message box.
Here is the full code:
Sub Example1()
Dim result As Double
result = Application.WorksheetFunction.SumIfs(Range(“B:B”), Range(“A:A”), “<3”)
MsgBox “Result: ” & result
End Sub
Example #2 – Date Criteria
In this example, we have a dataset consisting of three columns. Column A contains dates, Column B represents the types of fruits sold on each respective date, and Column C contains the corresponding sales amounts.
The objective is to calculate the sum of sales amounts for fruits sold within a specified date range in excel.
Step 1: In the new module, we first start by creating a new subroutine named Example2 and declare Variables named “result as a Double” data type to store the result of the calculation.
Step 2: In this step, we calculate the Sum with the VBA SUMIFS function.
We use the VBA SUMIFS function to sum the values in column C based on the condition that the corresponding date in column A falls within the specified date range (January 2024).
Step 3: Next, we will use the message box function to display the message.
Step 4: Finally, when the code is run, it calculates the sum of values in column C where the corresponding date in column A falls within the specified date range (January 2024) and displays the result in a message box.
Here is the full code:
Sub Example2()
Dim result As Double
result = Application.WorksheetFunction.SumIfs(Range(“C:C”), Range(“A:A”), “>=” & DateSerial(2024, 1, 1), Range(“A:A”), “<=” & DateSerial(2024, 1, 31))
MsgBox “Result: ” & result
End Sub
Example #3 – String Criteria with Wildcards
Suppose we have a dataset spanning columns A to F, where we have some information about fruits and the code assigned to the fruits.
The objective here is to calculate the sum of sales amounts (Column D) for fruits where the fruit type (Column E) starts with “A” and the fruit code (Column F) ends with “Z”.
Step 1: Similar to previous examples, we first define a new subroutine and declare a variable named “result as a Double” data type to store the result of the calculation.
Step 2: Next, we use the VBA SUMIFS function to sum the values in column D based on the condition that the corresponding value in column E starts with “A” and the value in column F ends with “Z”.
Step 3: We display the result using a message box.
Step 4: Now, when you run the code, it calculates the sum of values in column D where the corresponding value in column E starts with “A” and the value in column F ends with “Z” and displays the result in a message box.
Here is the full code:
Sub Example3()
Dim result As Double
result = Application.WorksheetFunction.SumIfs(Range(“D:D”), Range(“E:E”), “A*”, Range(“F:F”), “*Z”)
MsgBox “Result: ” & result
End Sub
Example #4 – VBA SUMIFS + VLOOKUP
In this example, we have a dataset representing student grades and their corresponding marks in different subjects.
We will use the VBA SUMIFS function to calculate the total sum of marks for students who got a “B” grade in the exam. And also we will use the Vlookup function to add the bonus scores to the result.
Step 1: Start by defining a subroutine and declare variables named result and lookup_value. The “result” will hold the total marks for Grade B students, while “lookup_value” will store the grade we are interested in, which is “B”.
Step 2: In this step, we will calculate the sum of values in column G using the VBA SUMIFS function.
The criteria for summing values are based on matching the values in column A with the lookup value stored in the variable lookup_value, which is currently set to “B”.
Step 3: In this step, we will use the VLOOKUP function in VBA to retrieve bonus points associated with Grade B from columns I and J.
This function searches for the value “B” in column I (Grades) and returns the corresponding bonus points from column J. The retrieved bonus points are then added to the result.
And then, we will use the message box function to display the result.
Step 4: Now save the macro and click on Run.
When you run the code, it calculates the sum of values in column G where the corresponding value in column A matches the specified criteria (“B”). It then adds the result of a VLOOKUP function by looking up the bonus value in the cell “J” and displays the result in a message box.
Here is the full code:
Sub Example4()
Dim result As Double
Dim lookup_value As String
lookup_value = “B”
result = Application.WorksheetFunction.SumIfs(Range(“G:G”), Range(“A:A”), lookup_value)
result = result + Application.WorksheetFunction.VLookup(lookup_value, Range(“I:J”), 2, False)
MsgBox “Total Marks for Grade B Students : ” & result
End Sub
Example #5 – VBA SUMIFS + SUM
Suppose we have a dataset and we want to calculate the sum of values in Column B where the corresponding values in Column A are greater than 3 and the values in Column C are not equal to 0 also additionally, add the sum of values in Column D.
We can achieve this using the VBA SUMIFS Function in the following way:
Step 1: Similar to the previous examples, we create a subroutine and then declare a variable named result as a Double data type in VBA to store the result of the calculation.
Step 2: In this step, we write the code to calculate “Sum” with the VBA SUMIFS and SUM functions.
We use the VBA SUMIFS function to sum the values in column B based on the condition that the corresponding value in column A is greater than 3 and the value in column C is not equal to 0.
We add the result of a SUM function to the SUMIFS result.
Step 3: We display the result using a message box.
Step 4: When the code is run, it calculates the sum of values in column B where the corresponding value in column A is greater than 3 and the value in column C is not equal to 0.
It then adds the result of a SUM excel function and displays the final result in a message box.
Here is the full code:
Sub Example5()
Dim result As Double
result = Application.WorksheetFunction.SumIfs(Range(“B:B”), Range(“A:A”), “>3”, Range(“C:C”), “<>0”) + Application.WorksheetFunction.Sum(Range(“D:D”))
MsgBox “Result: ” & result
End Sub
Important Things To Note
- VBA SUMIFS multiple columns enable summing values based on multiple conditions across multiple columns simultaneously, leveraging its capability to handle multiple criteria efficiently.
- The VBA SUMIFS array can handle complex calculations by processing data from multiple columns as arrays, providing flexibility and power in data analysis tasks.
- The VBA SUMIFS with dynamic range support allows for automatic adjustment of the range size based on changing data, ensuring accurate calculations without manual range updates.
- VBA SUMIFS across multiple sheets facilitates comprehensive data analysis by enabling the consolidation and summation of values meeting specified criteria from different sheets within the workbook.
Frequently Asked Questions (FAQs)
Yes, the VBA SUMIFS function allows you to use several criteria. Complex conditions can be applied simultaneously when additional criteria are added as a pair of range and criteria arguments. With this capability, users may do more thorough data analysis and get exact results based on predetermined parameters.
Yes, you can utilize dynamic ranges with the SUMIFS function. Ranges that dynamically modify themselves according to the data in a spreadsheet are known as dynamic ranges. This is usually accomplished with named ranges or formulae like OFFSET, INDEX, and MATCH.
Flexible and adaptive data analysis is made possible by the VBA SUMIFS function, which accepts these dynamic ranges as parameters. With the usage of dynamic ranges, users may handle different dataset sizes and automate operations without having to make manual modifications, which improves data manipulation jobs’ accuracy and efficiency.
Yes, array formulas in VBA are supported via the VBA SUMIFS function. You can calculate numerous values at once and get a range of outcomes by using array formulae.
You may apply complicated conditions over many ranges and quickly calculate sums based on a variety of factors when you use the VBA SUMIFS array formulas.
The VBA SUMIFS can do more complex data analysis jobs and provide users with comprehensive insights from their datasets thanks to the array formulae that extends its capabilities.
The number of criteria that VBA SUMIF and SUMIFS support is the primary distinction between them. Whereas SUMIFS can handle numerous criteria ranges and associated criteria, SUMIF can only manage one criteria.
Because of this, SUMIFS is more adaptable and appropriate for challenging data processing jobs requiring the simultaneous fulfillment of several requirements.
More flexibility and accuracy in data summarization based on several circumstances are offered by SUMIFS, enabling users to carry out more complex computations and produce customized outcomes.
Download Template
This article must help us understand the VBA SUMIFS and examples. You can download the template here to use it instantly.
Recommended Articles
Guide to VBA SUMIFS in Excel. Here we explain how to use VBA SUMIFS function, along with examples & downloadable excel template. You can learn more from the following articles –
Leave a Reply