## How to use the ROWS function

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.

#### Table of Contents

## 1. ROWS Function Syntax

ROWS(*array*)

## 2. ROWS Function Argument

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

## 3. ROWS Function Example

Formula in cell D3:

## 4. Count rows in an array

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

Formula in cell B3:

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

## 5. Count rows based on a condition

Formula in cell E3

### 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(*array*, *include*, [*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.

## 6. Count rows based on a list

Formula in cell F3:

### 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(*range*, *criteria*)

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(*array*, *include*, [*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.

## 7. Count rows in a delimited string

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:

### 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"}.

## 8. Count rows in 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:

### 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.

### Useful links

ROWS function - Microsoft

ROWS Formula in Excel: Explained

### 'ROWS' function examples

This post explains how to lookup a value and return multiple values. No array formula required.

Rattan asks: In my workbook I have three worksheets; "Customer", "Vendor" and "Payment". In the Customer sheet I have a […]

The array formula in cell F5 returns adjacent values from column C where values in column B matches the search […]

### Functions in 'Lookup and reference' category

The ROWS function function is one of many functions in the 'Lookup and reference' category.

## How to comment

How to add a formula to your comment<code>Insert your formula here.</code>

Convert less than and larger than signsUse html character entities instead of less than and larger than signs.

< becomes < and > becomes >

How to add VBA code to your comment[vb 1="vbnet" language=","]

Put your VBA code here.

[/vb]

How to add a picture to your comment:Upload picture to postimage.org or imgur

Paste image link to your comment.

Contact OscarYou can contact me through this contact form