Spreadsheet Practice

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)

ArgumentRequired?What it is
arrayRequiredA 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.

CellFormulaResultWhat it shows
D2=ROWS(A2:B5)4A two-column, four-row range has 4 rows
D3=SUM(B2:B5)/ROWS(B2:B5)39Average hours per week: the total divided by the number of weeks
D4=ROWS($B$2:B4)3A 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
WeekHoursResult
W1384
W24239
W3363
W440

Practice ROWS

Graded instantly, then re-checked on changed data.

Share of stores at target

In 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 seeWhy, and how to fix it
Expecting a count of filled cellsROWS counts rows whether or not they hold data. Use COUNTA to count cells that aren't empty.
Mixing up ROWS and ROWROW 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 .