The ROWS function

The ROWS function is a lookup and reference function which returns a count of the rows within the selected range of cells.

This is the format of the ROWS function:

=ROWS(Selected Range)

Here is an example of the ROWS function:

=ROWS(A3:F20) = 18 

ROWS returning the count of rows within the selected range

This function is valuable when you need to evaluate a formula using a defining aspect such as number of weeks, and you want that value to increment automatically as you progress down your template.

An example of a functional implementation of this would be to calculate the average sell-through percentage by period (week, month, etc…) of a product line. You can use the ROWS function to automatically calculate the divisor based on where in the template the formula sits. For this example, let’s say that the sell-through data for the first week begins in cell C3, sales units in B3, and received inventory in D3.

=($B3/$D3)/ROWS($C$3:$C3) = (10/100)/1

=($B4/$D4)/ROWS($C$3:$C4) = (15/100)/2

etc…