The Excel ROWS function counts how many rows a range or array contains — empty or not. It sounds almost too simple to matter, but it quietly powers some genuinely useful patterns: counting how many results FILTER returned, running counts that survive sorting, and size checks before array math. Its sideways twin is COLUMNS; don’t confuse it with ROW, which returns a position rather than a count.
Syntax
| Argument | Description | |
|---|---|---|
array | Required | A range, array, or array formula whose rows you want to count. |
It counts geometry, not content. =ROWS(A2:A100) is 99 whether those cells hold data, formulas, or nothing at all. That makes ROWS predictable — and makes COUNTA the right tool when you want “how many entries”.
Counting rows in ranges and arrays
The orders table below occupies A1:C7 — one header row plus six data rows (highlighted):
| A | B | C | |
|---|---|---|---|
| 1 | Order ID | Customer | Amount |
| 2 | 1001 | Acme Corp | $1,850 |
| 3 | 1002 | Bolt LLC | $920 |
| 4 | 1003 | Cedar Inc | $2,400 |
| 5 | 1004 | Delta Co | $1,310 |
| 6 | 1005 | Echo Ltd | $480 |
| 7 | 1006 | Foxtrot | $3,175 |
ROWS happily measures whole columns and arrays you type in directly:
Try it: interactive ROWS demo
Pick a range and see what ROWS returns — note how the column letters never affect the answer.
Practical uses: counting results, running counts, size checks
1. How many rows did FILTER return? Dynamic array results vary in size; wrap them in ROWS to count matches:
2. Running counts that survive sorting. Anchor the first cell and let the second expand as you copy down — a numbering trick many pros prefer to ROW because it ignores where the list sits on the sheet:
3. Last-row lookups. Pair ROWS with INDEX to fetch the bottom entry of a fixed-size range:
4. Sanity-check array math. Two ranges multiplied inside SUMPRODUCT must be the same height. A quick audit cell catches mismatches before they become silent errors:
Errors & common pitfalls
Pitfall: ROWS is not a data count. It measures the reference, not the contents. =ROWS(A2:A100) is always 99 even if only 12 cells are filled. Use COUNTA(A2:A100) to count entries, or point ROWS at a dynamic result like a FILTER spill.
Pitfall: ROWS vs ROW. ROW answers “where is it?”; ROWS answers “how many?”. =ROW(A5:A8) relates to position 5; =ROWS(A5:A8) is 4. Mixing them up is the most common bug in numbering formulas.
Pitfall: counting a spill? Reference the spill. If a FILTER formula in E2 spills results, count them with =ROWS(E2#) — the # grabs the whole spilled range. =ROWS(E2) is just 1.
#CALC! passed through. =ROWS(FILTER(...)) shows #CALC! when FILTER finds nothing and has no if_empty argument. Either give FILTER an if_empty value or wrap the whole thing in IFERROR.
Practice workbook
Frequently asked questions
What's the difference between ROWS and COUNTA?
What's the difference between ROWS and ROW?
How do I count how many results FILTER returned?
=ROWS(FILTER(range, criteria)). If the FILTER result is already spilling from, say, E2, count it with =ROWS(E2#). Add an if_empty argument to FILTER (or IFERROR around ROWS) so zero matches doesn’t show #CALC!.What does =ROWS(A:A) return?
How does =ROWS(B$2:B2) create a running count?
Master functions like this in one day
This page covers one function. Our Excel Formulas and Functions class covers the 30 that matter most — live, hands-on, taught by professionals in Dallas–Fort Worth, Houston, Austin, Oklahoma City, Denver, or online.
See the Formulas & Functions Class