Exceljet

Quick, clean, and to the point

Define range based on cell value

Excel formula: Define range based on cell value
Generic formula 
=SUM(firstcell:INDEX(data,rows,cols))
Explanation 

To define a range based on value(s) in another cell,  you can use the INDEX function. In the example shown, the formula in J7 is:

=SUM(C5:INDEX(data,J5,J6))

where "data" is the named range B5:G9.

How this formula works

This formula relies on on a specific behavior of INDEX - although it seems that INDEX returns the value at a particular location, it actually returns the address of the location. In most formulas, you wouldn't notice the difference, Excel simply evaulates the reference and returns the value. In this formula, we are using this feature to construct a dynamic range based on worksheet input.

Inside the sum function, the first reference is simply the first cell in the range that covers all possible cells:

=SUM(C5:

To get the last cell, we use INDEX.  Here, we give INDEX the named range "data", which is the maximum possible range of values, and also the values from J5 (rows) and J6 (columns). INDEX doesn't return a range, it only returns a single cell at that location, E9 in the example:

INDEX(data,J5,J6) // returns E9

The original formula is reduced to:

=SUM(C5:E9)

which returns 300, the sum of all values in C5:E5.

The formula in J8 is almost the same, but uses AVERAGE instead of SUM to calculate an average. When a user changes values in J5 or J6 the range is updated, and new results are returned.

Alternative with OFFSET

You can build a similar formulas with the OFFSET function, shown below:

=SUM(OFFSET(C5,0,0,J5,J6)) // sum
=AVERAGE(OFFSET(C5,0,0,J5,J6)) // average

OFFSET is designed to return a range, so the formulas are perhaps simpler to understand. However, OFFSET is a volatile function, and can cause performance problems when used in larger, more complex worksheets.

Author 
Dave Bruns

Excel Formula Training

Formulas are the key to getting things done in Excel. In this accelerated training, you'll learn how to use formulas to manipulate text, work with dates and times, lookup values with VLOOKUP and INDEX & MATCH, count and sum with criteria, dynamically rank values, and create dynamic ranges. You'll also learn how to troubleshoot, trace errors, and fix problems. Instant access. See details here.