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 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 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)-...
To get the last row number in a range, you can use a formula based on the ROW, ROWS, and MIN functions. In the example shown, the formula in cell F5 is:
=MIN(ROW(data))+ROWS(data)-1
where "data" is the named range B5...
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...
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 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 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...
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 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:$...
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 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...
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...
Related videos
One of the most useful features of Excel Tables is that they create a dynamic range. In this video, we'll take a closer look at how table ranges work.
This video provides a brief introduction to structured references. Structured references are an easy way to refer to tables in Excel formulas.
In this video, we'll look at different ways to remove a table from an Excel worksheet.
In this video, we'll look at how to create a dynamic named range with a Table. This is the simplest way to create a dynamic named range in Excel.
In this video, we'll introduce the idea of a dynamic range and show you why you might want to use one. Spoiler: they adjust formulas automatically when data changes.
Because tables support structured references, you can learn a lot about a table with simple formulas. In this video, we'll look at some formulas you can use to query a table.
Related functions
Good links
