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 rows.
Purpose
Get the number of rows in an array or reference.
Return value
Number of rows
Syntax
=ROWS (array)
Arguments
- array - A reference to a cell or range of cells.
Usage notes
Use the ROWS function to get the row count for a given reference or range. For example, there are 10 rows in the range A1:A10, so:
=ROWS(A1:A10) // returns 10
The range B1:D5 contains 5 rows:
=ROWS(B1:D5) // returns 5
You can also use the ROWS function to get a row count for an array constant:
=ROWS({1;2;3}) // returns 3
Notes
- Array can be a range or a reference to a single contiguous group of cells.
- Array can be an array constant or an array created by another formula.
ROWS formula examples
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 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...
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 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...
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 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)-...
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 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 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...
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),...
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...
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
