Explanation

In the example shown, the formula in B11 is:

=COUNTIFS(OFFSET(B$5,0,0,ROW()-ROW(B$5)-1,1),"<>")

Working from the inside out, the work of setting up a variable range is done by the OFFSET function here:

OFFSET(B$5,0,0,ROW()-ROW(B$5)-1,1) // variable range

OFFSET has five arguments and is configured like this:

  • reference = B$5, begin at cell B5, row locked
  • rows = 0, offset zero rows from starting cell
  • cols = 0, offset zero columns starting cell
  • height = ROW()-ROW(B$5)-1 = 5 rows high
  • width = 1 column wide

To work out the height of the range in rows, we use the ROW function like this:

ROW()-ROW(B$5)-1 // work out height

Since ROW() returns the row number of the “current” cell (i.e. the cell the formula lives in), we can simplify like this:

=ROW()-ROW(B$5)-1
=11-5-1
=5

With the above configuration, OFFSET returns the range B5:B9 directly to COUNTIFS:

=COUNTIFS(B5:B9,"<>") // returns 4

Notice the reference to B$5 in the above formula is a mixed reference , with the column relative and the row locked. This allows the formula to be copied to another column and still work. For example, once copied to C12, the formula is:

=COUNTIFS(OFFSET(C$5,0,0,ROW()-ROW(C$5)-1,1),"<>")

Note: OFFSET is a volatile function and can cause performance problems in large or complex worksheets.

With INDIRECT and ADDRESS

Another approach is to use a formula based on the INDIRECT and ADDRESS functions. In this case, we assemble a range as text, then use INDIRECT to evaluate the text as a reference. The formula in B11 would be:

=COUNTIFS(INDIRECT(ADDRESS(5,COLUMN())&":"&ADDRESS(ROW()-2,COLUMN())),"<>")

The ADDRESS function is used to construct a range like this:

ADDRESS(5,COLUMN())&":"&ADDRESS(ROW()-2,COLUMN())

In the first instance of ADDRESS, we supply row_number as the hardcoded value 5, and provide the column_number with the COLUMN function :

=ADDRESS(5,COLUMN()) // returns "$B$5"

In the second instance, we supply the “current” row_number minus 2, and the current column with the COLUMN function:

=ADDRESS(ROW()-2,COLUMN()) // returns "$B$9"

After concatenating these two values together, we have:

"$B$5:$B$9" // as text

Note this is a text string . To convert to a valid reference, we need to use INDIRECT:

=INDIRECT("$B$5:$B$9") // returns $B$5:$B$9 as valid range

Finally, the formula in B11 becomes:

=COUNTIFS($B$5:$B$9,"<>") // returns 4

Note: INDIRECT is a volatile function and can cause performance problems in large or complex worksheets.

Explanation

This formula relies on a specific behavior of INDEX — although it seems that INDEX returns the value at a particular location, it actually returns a reference to the location. In most formulas, you wouldn’t notice the difference – Excel simply evaluates the reference and returns the value. This formula uses 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:E9.

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