Author: Oscar Cronquist Article last updated on June 07, 2022

The ROWS function allows you to calculate the number of rows in a cell range.

The example above shows that cell range B3:B10 contains 8 rows.

1. ROWS Function Syntax

ROWS(array)

Back to top

2. ROWS Function Argument

array Required. A cell range for which you want to calculate the number of rows.

Back to top

3. ROWS Function Example

<span class='notranslate'>ROWS</span> function example

Formula in cell D3:

=ROWS(B3:B10)

Back to top

4. Count rows in an array

<span class='notranslate'>ROWS</span> function count rows in an array

The ROWS function also calculates the number of rows in an array.

Formula in cell B3:

=ROWS({20,95,67; 13,14,58; 96,74,28; 7,64,22})

The array has four rows. The ; (semicolon) character is a row delimiting character in an array.

Back to top

5. Count rows based on a condition

<span class='notranslate'>ROWS</span> function condition

Formula in cell E3

=ROWS(FILTER(B3:B10,B3:B10=D3))

Explaining formula

Step 1 - Logical expression

The equal sign lets you compare value to value, it is also possible to compare a value to an array of values. The result is either TRUE or FALSE.

B3:B10=D3

becomes

{"A"; "B"; "B"; "A"; "B"; "B"; "A"; "A"}="A"

and returns

{TRUE; FALSE; FALSE; TRUE; FALSE; FALSE; TRUE; TRUE}.

Step 2 - Filter values based on a condition

The FILTER function gets values/rows based on a condition or criteria.
FILTER(arrayinclude, [if_empty])

FILTER(B3:B10,B3:B10=D3)

becomes

FILTER({"A"; "B"; "B"; "A"; "B"; "B"; "A"; "A"}, {TRUE; FALSE; FALSE; TRUE; FALSE; FALSE; TRUE; TRUE})

and returns

{"A"; "A"; "A"; "A"}.

Step 3 - Count rows

ROWS(FILTER(B3:B10,B3:B10=D3))

becomes

ROWS({"A"; "A"; "A"; "A"})

and returns 4.

Back to top

6. Count rows based on a list

<span class='notranslate'>ROWS</span> function based on a list

Formula in cell F3:

=ROWS(FILTER(C3:C11,COUNTIF(E3:E4,C3:C11)))

Explaining formula

Step 1 - Which values equal any item in the list

The COUNTIF function counts the number of cells that meet a given condition.
COUNTIF(rangecriteria)

COUNTIF(E3:E4, C3:C11)

becomes

COUNTIF({"Clip"; "Pen"},{"Pen"; "Pencil"; "Clip"; "Pen"; "Clip"; "Pencil"; "Pen"; "Clip"; "Clip"})

and returns {1; 0; 1; 1; 1; 0; 1; 1; 1}.

Step 2 - Filter values based on array

The FILTER function gets values/rows based on a condition or criteria.
FILTER(arrayinclude, [if_empty])

FILTER(C3:C11,COUNTIF(E3:E4,C3:C11))

becomes

FILTER({"Pen"; "Pencil"; "Clip"; "Pen"; "Clip"; "Pencil"; "Pen"; "Clip"; "Clip"}, {1; 0; 1; 1; 1; 0; 1; 1; 1})

and returns

{"Pen"; "Clip"; "Pen"; "Clip"; "Pen"; "Clip"; "Clip"}.

Step 3 - Count rows

ROWS(FILTER(C3:C11,COUNTIF(E3:E4,C3:C11)))

becomes

ROWS({"Pen"; "Clip"; "Pen"; "Clip"; "Pen"; "Clip"; "Clip"})

and returns 7.

Back to top

7. Count rows in a delimited string

How to use the <span class='notranslate'>ROWS</span> function textsplit

The formula in cell D3 counts delimited values in a string located in cell B3, you can use any character or string a s a delimiting character.

Excel 365 dynamic array formula in cell C3:

=ROWS(TEXTSPLIT(B3,,"|"))

Explaining formula

Step 1 - Split string using a given delimiting character

The TEXTSPLIT function lets you split a string into an array across columns and rows based on delimiting characters.

TEXTSPLIT(B3,,";")

becomes

TEXTSPLIT("|7|45|31||37|98||6",,";")

and returns

{""; "7"; "45"; "31"; ""; "37"; "98"; ""; "6"}.

The semicolon is a delimiting character in arrays, however, they are determined by your regional settings. In other words, you may be using other delimtiing characters.

Step 2 - Count rows

ROWS(TEXTSPLIT(B3,,";"))

becomes

ROWS({""; "7"; "45"; "31"; ""; "37"; "98"; ""; "6"})

and returns 9. The values in the array are arranged vertically. An horizontal array would be using commas, like this: {"", "7", "45", "31", "", "37", "98", "", "6"}.

Back to top

8. Count rows in multiple cell ranges

<span class='notranslate'>ROWS</span> function multiple cell ranges

This example demonstrate how to count rows in three different sized cell ranges simultaneously and return total rows.

Formula in cell B12:

=ROWS(VSTACK(B3:B9,D3:D7,F3:F5))

Explaining formula

Step 1 - Join arrays

The VSTACK function combines cell ranges or arrays, it joins data to the first blank cell at the bottom of a cell range or array.
VSTACK(array1,[array2],...)

VSTACK(B3:B9, D3:D7, F3:F5)

becomes

VSTACK({7; 25; 82; 43; 25; 10; 21},{73; 13; 93; 25; 10; 65; 91},{43; 11; 97; 61; 4; 45; 91})

and returns

{7; 25; 82; 43; 25; 10; 21; 73; 13; 93; 25; 10; 65; 91; 43; 11; 97; 61; 4; 45; 91}.

Step 2 - Calculate rows

ROWS(VSTACK(B3:B9,D3:D7,F3:F5))

becomes

ROWS({7; 25; 82; 43; 25; 10; 21; 73; 13; 93; 25; 10; 65; 91; 43; 11; 97; 61; 4; 45; 91})

and returns 15.

Back to top