ROWS function
ROWS counts the rows in a range or array and returns that number. It is useful with spilled results, and it counts blank rows too, so use COUNTA to count filled cells.
Example
Read it as a sentence: count how many rows are in B1:E7, which gives 7.
Excel shows 7
B
C
D
E
1
Item
Category
Qty
Unit Value
2
Boxer cartridge
Tools
12
$3.50
3
Briar pipe
Smoking
7
$4.25
4
Candle
Household
20
$2.00
5
Cigar
Smoking
5
$10.00
6
Clay pipe
Smoking
15
$1.75
7
Commonplace book
Papers
9
$6.00
Items
Syntax
ROWS(array)
-
array(required) - A range or array.
Returns: The number of rows.
What Excel actually returns
Each formula below was run in desktop Excel. The result is exactly what the cell showed, errors included.
| Formula | Excel shows | Case |
|---|---|---|
=ROWS({1;2;3}) | 3 | Array |
=ROWS(5) | 1 | Scalar |
=ROWS("abc") | 1 | Text |
=ROWS(Items!B1:E7) | 7 | Range |
=ROWS(Data!A:A) | 1048576 | Whole column |
The Items sheet these formulas read
B
C
D
E
1
Item
Category
Qty
Unit Value
2
Boxer cartridge
Tools
12
$3.50
3
Briar pipe
Smoking
7
$4.25
4
Candle
Household
20
$2.00
5
Cigar
Smoking
5
$10.00
6
Clay pipe
Smoking
15
$1.75
7
Commonplace book
Papers
9
$6.00
Items
Watch out for
- Counts rows including blanks; use COUNTA for filled cells.
Where it works
- Excel
- ROWS is in every version of Excel from 2007 on.
- Google Sheets
- Same name, same behaviour for everyday use.
Part of Lookup and reference functions. Every result on this page was measured in desktop Excel, build 16.0.20527. Last updated .