ROWS function

ROWS returns the number of rows in a given array or range, counting every row including headers and blank rows within the reference.

=ROWS(array)

Generate a ROWS formula

Describe what you need. The generator will reach for ROWS where ROWS is the right tool, and tell you when it is not.

How to get a better answer
  • Name your columns by letter and by header: "column F (Net Value)" beats "the amount column".
  • State every condition, including the negatives — "not cancelled" changes the formula's shape.
  • Say where the data starts if it is not row 1, and whether it will grow.
  • Check the settings above match your spreadsheet: the wrong argument separator is a syntax error on your machine.

Arguments

How ROWS reads its arguments
arrayrequiredROWS →
ArgumentRequiredDescription
arrayRequiredA required range or array; can be a cell block, a spilled array, or any expression that evaluates to a rectangular array. Non-array inputs trigger #VALUE!.

Returns

A single numeric value representing the count of rows in the supplied array.

Availability

Excel: All · Google Sheets: Supported

Worked examples

1. Count total rows in the inventory table

SKUWarehouseOn HandReorder PointCost
A001East1201005
A002West801507.5
B001East2001803.25
=ROWS(A2:E5)

Result: 4

The range A2:E5 contains four horizontal lines: one header row and three data rows. ROWS simply counts each horizontal line, so the result is 4. This is useful for quickly confirming the size of a table before applying other functions.

2. Rows of items needing reorder

SKUWarehouseOn HandReorder PointCost
A001East1201005
B001East2001803.25
=ROWS(FILTER(A2:E5, C2:C5>D2:D5))

Result: 2

FILTER extracts only those rows where the "On Hand" quantity (column C) exceeds the "Reorder Point" (column D). In our data set, two rows meet that condition (A001 and B001). ROWS then counts the filtered array, returning 2. This pattern lets you dynamically size downstream calculations based on business rules.

3. Count rows in a single-column range

On Hand
120
80
200
=ROWS(C2:C5)

Result: 4

Even though only one column is selected, ROWS still counts the vertical entries. The range C2:C5 includes the header "On Hand" plus three numeric entries, giving a total of four rows. Use this when you need the length of a list independent of its width.

Common errors

Which ROWS error are you seeing?
ROWS returned an error#VALUE!
Wrap the value in a range reference (e.g., use A1:A1 instead of 5) or ensure the formula returns an array.
#REF!
Adjust the reference to a valid range, or restore the missing rows/columns if the reference should exist.
#N/A
Wrap the inner function with IFERROR or IFNA to supply a fallback array, ensuring ROWS receives a valid array.
ErrorWhy it happensHow to fix it
#VALUE!The argument supplied is not an array or range, such as a plain number or text string.Wrap the value in a range reference (e.g., use A1:A1 instead of 5) or ensure the formula returns an array.
#REF!The range reference points to cells that have been deleted or are otherwise invalid.Adjust the reference to a valid range, or restore the missing rows/columns if the reference should exist.
#N/AThe array argument itself evaluates to an error, for example when a nested VLOOKUP cannot find a match.Wrap the inner function with IFERROR or IFNA to supply a fallback array, ensuring ROWS receives a valid array.

Tips and when to use something else

  • Use COUNTA when you need to count non-blank cells rather than total rows, especially if the range contains many empty rows.
  • Combine ROWS with OFFSET to create dynamic ranges that expand or contract based on another cell's value.
  • If you need the number of columns instead of rows, use COLUMNS – it follows the same syntax but returns width.
  • Remember that ROWS counts header rows; subtract 1 if you only want the count of data records.

Related functions

VLOOKUPVLOOKUP searches the first column of a table and returns a value from a specified column in the same row, optionally using exact or approximate matching.INDEXINDEX returns the value or array of values from a specified position within a given range, based on row and optional column numbers.COLUMNSCOLUMNS returns the number of columns in a given array or range, letting you quickly determine a table's width.OFFSETOFFSET returns a reference to a range that is a specified number of rows and columns from a starting cell, optionally sized by height and width.CHOOSECOLSCHOOSECOLS returns the selected columns from a given array, preserving order and data types, and spills the result into adjacent cells.

Frequently asked questions

Why does ROWS return 0 when I use an empty range?
ROWS counts the number of rows in the supplied reference. If the reference contains no rows—such as a range that has been cleared entirely—the count is zero. This differs from COUNTA, which would also return zero but for a different reason (no non-blank cells).
Can ROWS be used with dynamic arrays that spill?
Yes. When a formula like SEQUENCE or FILTER returns a spilled array, you can feed that directly into ROWS. The function will count the rows of the spilled result, updating automatically as the source data changes.
What’s the difference between ROWS(A:A) and ROWS(A1:A1000)?
ROW(A:A) returns the total number of rows in the entire column (1,048,576 in modern Excel), while ROWS(A1:A1000) is limited to the explicit 1,000-row block you defined. Use the narrower reference to avoid performance hits on very large sheets.
How do I count rows that meet multiple criteria without using FILTER?
You can nest ROWS inside a SUMPRODUCT or use the newer BYROW function with a lambda that returns 1 for rows meeting the criteria and then sum the results. However, FILTER + ROWS is usually the most readable approach.

Need a different formula?

The full generator is not scoped to one function — describe any spreadsheet problem and it will pick.

Open the formula generator

Reviewed 2026-09-17