Dynamically change multiple sum ranges

How to dynamically change multiple sum ranges

Example: Dynamically change multiple sum ranges

METHOD 1. Dynamically change multiple sum ranges using SUMIFS, INDEX and MATCH functions

EXCEL

=SUMIFS(INDEX(B9:F16,,MATCH(C5,B9:F9,0)),B9:B16,C6,C9:C16,C7)

The formula uses a combination of the Excel SUMIFS, INDEX and MATCH functions to sum the values that are associated with multiple sum range criteria (Bread and Brand A).

METHOD 1. Dynamically change sum range using SUMIFS, INDEX and MATCH functions using VBA

VBA

Sub Dynamically_change_multiple_sum_ranges()

'declare a variable
Dim ws As Worksheet

Set ws = Worksheets("Analysis")

'apply the formula
ws.Range("H10") = WorksheetFunction.SumIfs(WorksheetFunction.Index(ws.Range("B9:F16"), 0, WorksheetFunction.Match(ws.Range("C5"), ws.Range("B9:F9"), 0)), ws.Range("B9:B16"), ws.Range("C6"), ws.Range("C9:C16"), ws.Range("C7"))

End Sub

OBJECTS
Worksheets: The Worksheets object represents all of the worksheets in a workbook, excluding chart sheets.
Range: The Range object is a representation of a single cell or a range of cells in a worksheet.

PREREQUISITES
Worksheet Name: Have a worksheet named Analyst.
Sum Range: Ensure that the data you want sum is captured in range ("B9:F16").

ADJUSTABLE PARAMETERS
Output Range: Select the output range by changing the cell reference ("H10") in the VBA code to any cell in the worksheet, that doesn't conflict with the formula.
Sum Range: Select the range that you want to sum from by changing the range ("B9:F16") to any range in the worksheet, that doesn't conflict with the formula.

Explanation about the formula used to dynamically change multiple sum ranges using SUMIFS, INDEX and MATCH functions

EXPLANATION

EXPLANATION
This tutorial shows how to dynamically change multiple sum ranges using the Excel SUMIFS, INDEX and MATCH functions.

FORMULA
=SUMIFS(INDEX(data_range,,MATCH(row_criteria,row_range,0)),criteria_range_1,criteria1,criteria_range_2,criteria2)

ARGUMENTS
data_range: The range that captures the data to sum from.
row_range: The row range which captures the row criteria against which to sum.
row_criteria: The row criteria that is used to determine which of the cells, from the data range, should be summed.
criteria_range_1: The range which captures the first criteria against which to sum.
criteria_1: The first criteria that is used to determine which of the cells, from the data range, should be summed.
criteria_range_2: The range which captures the second criteria against which to sum.
criteria_2: The second criteria that is used to determine which of the cells, from the data range, should be summed.