ROWS Function

The ROWS function returns the count of rows in a given reference as a number. For example, =ROWS(A1:A5) returns 5, since the range A1:A5 contains 5 rows. ROWS takes just one argument, called array, which can be a range or array.

Examples

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:F10 so the formula below returns 10:

=ROWS(A1:F10) // returns 10

The range A1:Z100 contains 100 rows, so the formula below returns 100:

=ROWS(A1:Z100) // returns 100

You can also use the ROWS function to get a row count for an array constant:

=ROWS({1;2;3;4;5}) // returns 5

Although there is no built-in function to count the number of cells in a range, you can use the ROWS function together with the COLUMNS function like this:

=COLUMNS(range)*ROWS(range) // total cells
=COLUMNS(A1:Z100)*ROWS(A1:Z100) // returns 2600

This article explains this formula in more detail.

Count rows in data of unknown size

The examples above count rows in a known range. When you don't know where the data ends, for example in a CSV file you have just opened, a full column reference won't help: =ROWS(A:A) returns 1,048,576, the number of rows in a worksheet. Instead, give ROWS a trim reference:

=ROWS(A:.A)

The dot after the colon tells Excel to trim empty rows from the end of the range, so A:.A runs from A1 to the last cell in column A that isn't empty. Empty cells above that point are still counted. With a header in row 1 and 10 records below it, the formula returns 11, so subtract 1 to count records only:

=ROWS(A:.A)-1 // returns 10

If the last record might be empty in column A, trim several columns at once. This version counts down to the last row with a value in any column from A to Z:

=ROWS(A:.Z)-1

You can also use the TRIMRANGE function, which works like a trim reference written as a function: =ROWS(TRIMRANGE(A:A))-1. Trim references and TRIMRANGE are available in Excel 365 only. In Excel 2024 and earlier, use the COUNTA function, which counts cells that aren't empty:

=COUNTA(A:A)-1 // returns 10

COUNTA skips empty cells, so pick a column with a value in every record, such as an ID column. In a column with gaps, COUNTA returns a count that is too low.

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.
• To count columns, see the COLUMNS function.
• To get row numbers, see the ROW function.
• To lookup a row number, see the MATCH function.