VBA SUMIFS

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.

VBA SUMIFS - Example

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.

VBA SUMIFS - Example- function

When you run this code, it will apply the SUMIFS formula to cell D10, providing the sum of values meeting the specified criteria.

VBA SUMIFS - Example Output

Syntax

The syntax for the SUMIFS function in VBA is:

Application.WorksheetFunction.SumIfs(sum_range, criteria_range1, criteria1, [criteria_range2], [criteria2], …)

SyntaxDescription
sum_rangeThe range of cells that you want to sum.
criteria_range1The first range of cells that you want to apply criteria to. It is where the first condition will be evaluated.
criteria1The 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.
Key Takeaways
  1. The VBA SUMIFS function is a powerful tool for summing values based on multiple criteria in Excel VBA.
  2. It extends the functionality of the SUMIF function by allowing multiple conditions to be specified.
  3. 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.
  4. 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.
How to Use VBA SUMIFS Function 1
  • Step 2: Insert a new module by clicking on “Insert” in the menu and selecting “Module.”
How to Use VBA SUMIFS Function 1-1
  • 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.

VBA SUMIFS - Example 1

Step 1: In the new module, we start by defining a subroutine named Example1.

VBA SUMIFS - Example 1-1

Step 2: We declare a variable named result as a Double data type to store the result of the calculation.

VBA SUMIFS - Example 1-2

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.

VBA SUMIFS - Example 1-3

Step 4: We display the result using a message box.

VBA SUMIFS - Example 1-4

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.

VBA SUMIFS - Example 1-5

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.

VBA SUMIFS - Example 2

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.

VBA SUMIFS - Example 2-1

Step 2: In this step, we calculate the Sum with the VBA SUMIFS function.

VBA SUMIFS - Example 2-2
VBA SUMIFS - Example 2-3
VBA SUMIFS - Example 2-4

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.

VBA SUMIFS - Example 2-5

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.

VBA SUMIFS - Example 2-6

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”.

VBA SUMIFS - Example 3

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.

VBA SUMIFS - Example 3-1

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”.

VBA SUMIFS - Example 3-2
VBA SUMIFS - Example 3-3

Step 3: We display the result using a message box.

VBA SUMIFS - Example 3-4

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.

VBA SUMIFS - Example 3-5

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.

VBA SUMIFS - Example 4
VBA SUMIFS - Example 4-1

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”.

VBA SUMIFS - Example 4-2

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”.

VBA SUMIFS - Example 4-3

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.

VBA SUMIFS - Example 4-4

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.

VBA SUMIFS - Example 4-5

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.

VBA SUMIFS - Example 5

Step 2: In this step, we write the code to calculate “Sum” with the VBA SUMIFS and SUM functions.

VBA SUMIFS - Example 5-1

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.

VBA SUMIFS - Example 5-4

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.

VBA SUMIFS - Example 5-5

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

  1. VBA SUMIFS multiple columns enable summing values based on multiple conditions across multiple columns simultaneously, leveraging its capability to handle multiple criteria efficiently.
  2. The VBA SUMIFS array can handle complex calculations by processing data from multiple columns as arrays, providing flexibility and power in data analysis tasks.
  3. 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.
  4. 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)

1. Can I use multiple criteria with SUMIFS in VBA?

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.

2. Can I use dynamic ranges with SUMIFS in VBA?

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.

3. Does SUMIFS support array formulas in VBA?

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.

4. What is the difference between VBA SUMIF and SUMIFS?

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.

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 –

Reader Interactions

Leave a Reply

Your email address will not be published. Required fields are marked *