Explanation
Dynamic ranges are also known as expanding ranges because they automatically expand and contract to accommodate new or deleted data. You can see a video demo of this approach here . This formula uses the OFFSET function to generate a range that expands and contracts by adjusting height and width based on a count of non-empty cells:
=OFFSET(B5,0,0,COUNTA($B$5:$B$100),COUNTA($B$4:$Z$4))
The first argument in OFFSET represents the first cell in the data (the origin), which in this case is cell B5. The next two arguments are offsets for rows and columns and are supplied as zero.
The last two arguments represent height and width. Height and width are generated on the fly by using COUNTA, which makes the resulting reference dynamic.
For height, we use the COUNTA function to count non-empty values in the range B5:B100. This assumes no blank values in the data, and no values beyond B100. COUNTA returns 6.
For width, we use the COUNTA function to count non-empty values in the range B5:Z5. This assumes no header cells, and no headers beyond Z5. COUNTA returns 6.
At this point, the formula looks like this:
=OFFSET(B5,0,0,6,6)
With this information, OFFSET returns a reference to B5:G10, which corresponds to a range 6 rows height by 6 columns across.
Note: The ranges used for height and width should be adjusted to match the worksheet layout.
Variation with full column/row references
You can also use full column and row references for height and width like so:
=OFFSET($B$5,0,0,COUNTA($B:$B)-2,COUNTA($4:$4))
Note that height is being adjusted with -2 to take into account header and title values in cells B4 and B2. The advantage of this approach is the simplicity of the ranges inside COUNTA. The disadvantage comes from the huge size full columns and rows — care must be taken to prevent errant values outside the range, as they can easily throw off the count.
Determining the last row
There are several ways to determine the last row (last relative position) in a set of data, depending on the structure and content of the data in the worksheet:
- Last row in mixed data with blanks
- Last row in mixed data with no blanks
- Last row in text data
- Last row in numeric data
Explanation
In this example, we have dates in B5:B16 and sales in C5:C16. Both ranges are named ranges . The goal is to create a dynamic range between two specific dates: the start date in cell F5 and the end date in cell F6. We then use a formula in F8 to sum the dynamic range, and a formula in F9 to count the dynamic range. In the current version of Excel, this problem can be easily solved with the XLOOKUP function. In older versions of Excel without XLOOKUP, you can use INDEX and MATCH. Both approaches are described below. This problem is a nice demonstration of how both XLOOKUP and INDEX return a valid reference that can be used like any other cell reference.
Range operator
One of Excel’s most common operators is the colon (:), also known as a “range operator”. The range operator (:) is used to construct ranges. For example, to create a reference from cell A1 to cell A9, you would use a range like this:
=A1:A9
To sum all values in this range, you would simply embed the range in the SUM function :
=SUM(A1:A9)
In this problem, we want to do essentially the same thing. However, the catch is that we want the range to be dynamic so that the references to A1 and A9 are the result of user input. Conceptually, the generic syntax for what we want looks like this:
=SUM(lookup1:lookup2)
In the code above, lookup1 should return a reference to the first cell in the range (the start date in F5), and lookup2 should be a reference to the last cell in the range (the end date in F6).
XLOOKUP function
The trick in solving this problem is to understand that the XLOOKUP function returns a reference and not just a value. This is not obvious in Excel, because even though XLOOKUP returns a reference, Excel will then immediately return the value at that reference . Nevertheless, the reference is there and can be used in other ways. In this case, we use this feature to assemble a range based on the results from two separate XLOOKUP formulas like this:
=SUM(XLOOKUP(F5,date,sales):XLOOKUP(F6,date,sales))
Inside the SUM function, notice we are using two separate XLOOKUP formulas joined with the range operator (:). The first XLOOKUP formula locates the start date in F5 in column B and returns a reference to the corresponding cell in column C:
XLOOKUP(F5,date,sales) // returns C5
With the lookup_value provided as F5, the lookup_array given as date (B5:B16), and the return_array given as sales (C5:C16), XLOOKUP matches January 1 in cell B5 and returns a reference to cell C5 as a result. The second XLOOKUP formula is configured in the same way, except the lookup_value is F6 (the end date):
XLOOKUP(F6,date,sales) // returns C10
With June 1 in cell F6, the result from XLOOKUP is a reference to C10. Putting this all together, the formula in F8 is evaluated by Excel like this:
=SUM(XLOOKUP(F5,date,sales):XLOOKUP(F6,date,sales))
=SUM(C5:C10)
=7075
When the dates in F5 or F6 are changed, XLOOKUP returns new references and the range is dynamically updated. The SUM then returns a new result. To count results instead of summing results, just replace the SUM function with the COUNT function. In the worksheet shown, the formula in cell F9 looks like this:
=COUNT(XLOOKUP(F5,date,sales):XLOOKUP(F6,date,sales))
For a detailed overview of XLOOKUP, see How to use the XLOOKUP function .
INDEX and MATCH
In older versions of Excel without the XLOOKUP function, this problem can be solved in the same way with an INDEX and MATCH that uses the same structure:
=SUM(INDEX(sales,MATCH(F5,date,0)):INDEX(sales,MATCH(F6,date,0)))
This works because the INDEX function, like XLOOKUP, returns a reference when the array provided to INDEX is a range. Notice the range operator (:) sits between two separate INDEX and MATCH formulas. The first INDEX and MATCH formula locates the date entered in F5 (January 1) in column B and returns a corresponding reference from the sales amounts in column C:
INDEX(sales,MATCH(F5,date,0)) // returns C5
The result is a reference to cell C5 since cell B5 contains January 1. The second INDEX and MATCH formula is the same, except that the lookup value comes from cell F6:
INDEX(sales,MATCH(F6,date,0)) // returns C10
The result is a reference to cell C10 since cell B10 contains June 1. Excel evaluates this formula in the same way as the XLOOKUP version above, with exactly the same result:
=SUM(INDEX(sales,MATCH(F5,date,0)):INDEX(sales,MATCH(F6,date,0)))
=SUM(C5:C10)
=7075
If the dates in F5 and F6 are changed, the INDEX and MATCH formulas return new references, and the range they create is dynamically updated. The SUM function then returns a new result. To count results instead of summing results, just replace the SUM function with the COUNT function:
=COUNT(INDEX(sales,MATCH(F5,date,0)):INDEX(sales,MATCH(F6,date,0)))
For a detailed overview of INDEX with MATCH see: How to use INDEX and MATCH .