Dynamically change sum range

This tutorial shows how to dynamically change sum range

Example: Dynamically change sum range

Dynamically change sum range

METHOD 1. Dynamically change sum range using SUMIF function

EXCEL

=SUMIF(B8:B15,C5,INDEX(B8:E15,,MATCH(C6,B8:E8,0)))
The formula uses a combination of the Excel SUMIF, INDEX and MATCH functions to sum the values that are associated with two criteria (Bread and Shop B).

METHOD 1. Dynamically change sum range using SUMIF function using VBA

VBA

Sub Dynamically_change_sum_range_using()
'declare a variable
Dim ws As Worksheet
Set ws = Worksheets("Analysis")
'apply the formula
ws.Range("G9") = WorksheetFunction.SumIf(ws.Range("B8:B15"), ws.Range("C5"), WorksheetFunction.Index(ws.Range("B8:E15"), 1, WorksheetFunction.Match(ws.Range("C6"), ws.Range("B8:E8"), 0)))

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 ("B8:E15").

ADJUSTABLE PARAMETERS
Output Range: Select the output range by changing the cell reference ("G9") 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 ("B8:E15") to any range in the worksheet, that doesn't conflict with the formula.

Explanation about the formula used to dynamically change sum range

EXPLANATION

EXPLANATION
This tutorial shows how to dynamically change sum range using the SUMIF, INDEX and MATCH functions.
FORMULA
=SUMIF(criteria_range_1,criteria1,INDEX(data_range,,MATCH(criteria2,criteria_range_2,0)))

ARGUMENTS
data_range: The range that captures the data to sum from.
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.