ROWS function
Quick answer
ROWS counts how many rows a range or array has, whether or not the cells contain anything.
- Syntax
=ROWS(array)- Most common use
=ROWS(A2:B5)returns 4 (A two-column, four-row range has 4 rows)
Use it to size a calculation to a range: a share of rows that meet a target, the number of records a FILTER returned, or a running count that grows as a formula is filled down.
- Available in
- Every current version
- Category
- Lookup functions
- Returns
- The number of rows.
- Google Sheets
- Works the same way in Google Sheets.
Syntax
=ROWS(array)
| Argument | Required? | What it is |
|---|---|---|
array | Required | A range, an array, or a formula that returns an array. |
Examples
Each result below is calculated from the sample sheet by our formula engine, the same one that grades the practice.
| Cell | Formula | Result | What it shows |
|---|---|---|---|
| D2 | =ROWS(A2:B5) | 4 | A two-column, four-row range has 4 rows |
| D3 | =SUM(B2:B5)/ROWS(B2:B5) | 39 | Average hours per week: the total divided by the number of weeks |
| D4 | =ROWS($B$2:B4) | 3 | A range that grows as you fill down counts how far down the list you are |
Try it live
This is the sample sheet. Change any number or text, or edit a formula, and the results update.
Show the sample data as a table
| Week | Hours | Result | |
| W1 | 38 | 4 | |
| W2 | 42 | 39 | |
| W3 | 36 | 3 | |
| W4 | 40 |
Practice ROWS
Graded instantly, then re-checked on changed data.
Share of stores at target
E2, return the share of stores that sold at least the target in D2: the number at or above target divided by the number of stores. Use ROWS to count the stores.Common mistakes
| What you see | Why, and how to fix it |
|---|---|
| Expecting a count of filled cells | ROWS counts rows whether or not they hold data. Use COUNTA to count cells that aren't empty. |
| Mixing up ROWS and ROW | ROW returns a row number, such as 5 for row 5. ROWS returns how many rows a range spans. |
Tips
- =ROWS(FILTER(A2:C100,C2:C100>500)) tells you how many records a filter returned.
- =ROWS($A$2:A2), filled down, numbers a list 1, 2, 3 and keeps working if the table moves.
Questions
How do I count the rows in a range?
=ROWS(A2:A100) counts every row in the range. To count only rows with data, use =COUNTA(A2:A100).
What is the difference between ROW and ROWS?
ROW gives the row number of a cell. ROWS gives the number of rows in a range.
Lessons that use ROWS
Answers that use ROWS
In other languages
ROWS is FILAS in Spanish Excel and ZEILEN in German Excel. Each page has the syntax with semicolons, the examples and the exercise in that language. For other functions and languages, use the Function Translator.
Updated Checked by running: every formula on this page was run by our formula engine on .