A to Z of Excel Functions: The ROWS Function

Welcome back to our regular A to Z of Excel Functions blog.  Today we look at the ROWS function.

The ROWS function

The ROWS function returns the row number of a reference:

The ROWS function has the following argument:

• array: this argument is required and represents an array, an array formula or a reference to a range of cells for which you want to know the number of rows.

Please see my examples below:

ROWS is sometimes associated with the HLOOKUP function.  HLOOKUP always looks for the lookup_value in the first row of a table (the table_array) and then returns a corresponding value so many rows below, determined by the row_index_number, e.g.

In this above example, the formula in cell G25 seeks the value 2 in the first row of the table G12:M18 and returns the corresponding value from the seventh row of the table (returning 96). 

Pretty easy to understand; so far so good.  So what goes wrong?  Well, what happens if you add or remove a row from the table range?  Adding (inserting) a row gives us the wrong value:

With a row inserted, the formula contains hard code (7) and therefore, the seventh row (row 18, not row 19) is still referenced, giving rise to the wrong value.  Deleting a row instead is even worse:

Now there are only six rows so the formula returns #REF!  Oops.

It is possible to make the row index number dynamic using the ROWS function (that’s right, every ROWS has its HLOOKUP function):

ROWS(reference) counts the number of rows in the reference.  Using the range F12:F18, this formula will now keep track of how many rows there are between the lookup row (12) and the result row (18).  This will prevent the problems illustrated above.

We’ll continue our A to Z of Excel Functions soon.  Keep checking back – there’s a new blog post every other business day.