Excel ROWS Function
Summary
The Excel ROWS function returns the count of rows in a given reference. For example, ROWS(A1:A3) returns 3, since the range A1:A3 contains 3 columns.
Purpose
Get the number of rows in an array or reference.
Return value
A number representing row count.
Syntax
=ROWS (array)
Arguments
- array - A reference to a cell or range of cells.
Usage notes
Use ROWS to get the column count of a reference. For example, the formula =ROWS(A1:A10) returns the number 10.
Array can be an array, an array formula, or a reference to a single contiguous group of cells.
ROWS formula examples
To get a random value from a table or list in Excel, you can use the INDEX function with help from the RANDBETWEEN and ROWS functions.
In the example shown, the formula in G7 is:
=INDEX(data,RANDBETWEEN(1,ROWS(data...
You can get the first column (i.e. the starting column number) in a range with a formula based on the COLUMN function.
In the example shown, the formula in cell F5 is:
=MIN(COLUMN(data))
where data is a named range...
To create a dynamic date list, you can use a formula that increments a start date to create and display additional dates. In the example shown, the formula in B5 is:
=start+ROWS($B$5:B5)-1-offset
where "start" is the...
To count rows in an Excel table, you can use the ROWS function. In the example shown, the formula in I4 is:
=ROWS(Table1)
Note: with just the table name, ROWS will count data rows only.
How this formula works
This...
You can get the last row in a range with a formula based on the ROW function.
In the example shown, the formula in cell F5 is:
=MIN(ROW(data))+ROWS(data)-1
where data is a named range for B5:D10
How this formula...
To get the full address of a named range with an Excel formula, you can use the ADDRESS function together with the ROW and COLUMN functions. In the example shown, the formula in G5 is:
=ADDRESS(ROW(data),COLUMN(data),...
If you need to count the total number of cells in a rectangular range, you can do so with the ROWS and COLUMNS functions. In the generic form of the formula (above) rng represents a range of cells.
In the example, the...
To get the address of the first cell in a named range, you can use the ADDRESS function together with ROW and COLUMN functions.
In the example shown, the formula in G6 is:
=ADDRESS(ROW(data)+ROWS(data)-1,COLUMN(data...
To retrieve and display values sorted with a helper column, you can use an INDEX and MATCH formula, with a little help from the ROWS function. In the example shown, the formula in F5 is:
=INDEX(sales,MATCH(ROWS($D$5:$...
To extract multiple matches to separate cells, in separate rows, you can use an array formula based on INDEX and SMALL. In the example shown, the formula in E5 is:
{=IFERROR(INDEX(names,SMALL(IF(groups=E$4,ROW(names)-...
If you need to count the number of rows in a range, use the ROWS function. In the generic form of the formula (above) rng represents a range of cells.
In the example, the active cell contains this formula:
=ROWS(B4:...
To calculate the average for the last N values n an Excel table (i.e. last 3 rows, last 5 rows, etc.) you can use the AVERAGE function together with the INDEX and ROWS functions. In the example shown, the formula in F5...
Related videos
In this video, we show you how to add a message at the top of a filtered list that displays total and visible items in the list.
In this video gives a brief introduction to structured references. Structured references is just a fancy name for formulas that use table names instead of normal cell references. This sounds boring, but it's actually cool and useful.
Related functions
Good links
