If you want to know how many rows a range or table covers without scrolling down and counting them yourself, the ROWS function in Google Sheets is what you want.
It takes a range and gives you back the number of rows in it. In this article I’ll walk you through how ROWS works with three quick examples.
ROWS Function Syntax in Google Sheets
Here is how you write the ROWS function.
=ROWS(range)
- range – the range or array whose rows you want to count. This can be a single column, a single row, or a full block of cells.
When to Use ROWS Function
- Find out how many rows a table or list has without counting them by hand.
- Build a running row number that updates as your data grows.
- Drive other formulas that need a row count, like the last row in a range.
- Check the size of a range before passing it to a lookup or an array formula.
Example 1: Count the Rows in a Range
Let’s start with the simplest case, counting a plain list.
Below is the dataset, a single column of nine names sitting in A2 to A10.
The goal is to find out how many rows that list of names takes up.
Here is the formula:
=ROWS(A2:A10)
ROWS looks at the range A2:A10 and counts the rows in it. That range runs from row 2 to row 10, so the result is 9.
Notice it counts the rows you gave it, not the rows that actually have data. If half those cells were blank, you’d still get 9, because the range itself is nine rows tall.
Pro Tip: To count columns instead of rows, use the COLUMNS function. It’s the column-count counterpart to ROWS and works exactly the same way.
Example 2: Create a Running Row Number
Here’s a handy trick for numbering rows as you fill data down.
Below is the dataset, a single column of fruit names in A2 to A6.
The goal is to put 1, 2, 3 and so on next to each item, with the numbers growing automatically as you add rows.
Here is the formula:
=ROWS($A$2:A2)
The trick is the mix of references inside the range. The start, $A$2, is locked with dollar signs so it never moves. The end, A2, has no dollar signs, so it shifts down as you fill the formula.
In B2 the range is $A$2:A2, which is one row tall, so ROWS returns 1. In B3 it becomes $A$2:A3, two rows tall, so you get 2. Each row down the range grows by one, which gives you 1, 2, 3, 4, 5.
Example 3: Count Rows in a Two-Dimensional Range
ROWS doesn’t care how wide a range is, only how tall.
Below is the dataset, a small product table with a Product column plus Q1 and Q2 sales in columns A to C.
The goal is to count the data rows in that table, ignoring the three columns it spans.
Here is the formula:
=ROWS(A2:C5)
The range A2:C5 is three columns wide and four rows tall. ROWS only counts rows, so the width makes no difference and the result is 4.
This is the part people miss. ROWS always ignores columns. If you need the column count for the same block, reach for COLUMNS instead.
Tips & Common Mistakes
- ROWS counts the rows in the range you hand it, not the rows that contain data. A range with blank cells still returns its full height.
- For a single cell like
=ROWS(A2)you’ll get 1, since one cell is one row tall. To count a column’s worth of cells, give it a real range. - ROWS counts how many rows a range has, not the position of a row. If you need the column number of a single cell, that’s the job of the COLUMN function.
ROWS is a small function but it shows up everywhere once you start building dynamic formulas. Counting a list, numbering rows, sizing a range, it handles all of it with one argument.
Try it on your own data and pair it with COLUMNS when you need both dimensions.
List of All Google Sheets Functions
Related Google Sheets Functions / Articles: