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

### 'ROWS' function examples

The following 93 articles contain the ROWS function.

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 […]

This article demonstrates formulas that extract values that exist only in one column out of two columns. There are text […]

The image above demonstrates an array formula in cell B11 that extracts values that only exist in List 1 (B3:B7) […]

The array formula in cell B3 creates a list of dates based on the date ranges displayed in D3:E7, it […]

The array formula in cell D4 extracts the start dates for date ranges in cell range B3:B30, the array formula […]

This article demonstrates how to construct a formula that counts unique distinct values based on a condition. The image above […]

The drop down calendar in the image above uses a "calculation" sheet and a named range. You can copy the drop-down […]

This article describes how to create a drop-down list populated with sorted values from A to Z. The sorted list […]

Question: How do I create a list of dates with blanks between each quarter? (Q1, Q2, Q3 and Q4) Answer: […]

Excel has a great built-in tool for creating number series named Autofill. The tool is great, however, in some situations, […]

The image above shows an array formula in cell B3 that calculates numbers based on the numerical ranges in cell […]

Table of Contents Delete blanks and errors in a list How to find errors in a worksheet 1. Delete blanks […]

This article demonstrates how to set up dependent drop-down lists in multiple cells. The drop-down lists are populated based on […]

This article demonstrates a scoreboard, displayed to the left, that sorts contestants based on total scores and refreshes instantly each […]

The following image shows you a data set in columns B and C. The formula in cell E2 extracts a […]

Lookup with criteria and return records.

Question: I have a list and I want to filter out all rows that have a value (Column C) that […]

Sam asks: One more question for the Calendar that you have set up above can we have a excel formula […]

The array formula in cell B3 extracts overlapping dates based on the date ranges in columns D and E. What's […]

This article demonstrates formulas that extract duplicate values from a column, however, column D contains values that you don't want […]

I this article I will show you how to get numerical values from a cell range manually and using an […]

Question: I second G's question: can this be done for more than 3? i.e. (Instead of last name, middle, first) […]

This article demonstrates ways to extract shared values in different cell ranges, two and three cell ranges. The Excel 365 […]

The array formula in cell D3 returns the the length of longest consecutive sequence of a value in column A. Cell […]

This article demonstrates two formulas that extract distinct values from a filtered Excel Table, one formula for Excel 365 subscribers […]

The formula in cell B7 and C7 extracts whole weeks within the given date range in cell B3 and C3. […]

Array formula in D2: =INDEX($A$2:$A$21, MATCH(SMALL(IF(COUNTIF($B$2:$B$21, $B$2:$B$21)>1, COUNTIF($A$2:$A$21, "<"&$A$2:$A$21), ""),ROWS($A$1:A1)), COUNTIF($A$2:$A$21, "<"&$A$2:$A$21), 0)) Array formula in E2: =INDEX($B$2:$B$21, MATCH(SMALL(IF(COUNTIF($B$2:$B$21, $B$2:$B$21)>1, […]

This article demonstrates formulas and Excel tools that extract duplicates based on three conditions. The first and second condition is […]

I will in this blog post demonstrate a formula that extracts common records (shared records) from two data sets in […]

A unique value is a value that only exists once in a list. A unique distinct list contains all cell values […]

The image above shows a formula in cell D3 that extract dates from column B. Column B contains dates in […]

The formula in cell B8, shown above, extracts dates not included in the specified date ranges, in other words, dates […]

The image above demonstartes an array formula in cell B34 that extracts empty hours in a weekly calendar. I have created […]

Excelxor is such a great website for inspiration, I am really impressed by this post Which numbers add up to […]

In my previous post, I described how to build a dynamic stock chart that lets you easily adjust the date […]

This article demonstrates a formula and a VBA macro that returns every n-th row from a given cell range. The […]

This article demonstrates a formula that extracts unique values from a column also considering upper and lower characters (case sensitive). […]

Question: How do i remove common values between two lists? Answer: The solution in this article, removes common values and […]

This article describes a formula that returns all rows containing at least one digit 0 (zero) to 9. What's on […]

This article shows how to create a formula that sorts numbers from small to large excluding zeros. I will also […]

This article explains how to substitute part of a formula across all cells in a worksheet. It is easier than […]

I will in this article demonstrate three different techniques to sort a data set in Excel. The first method sorts […]

Question: How do I find missing numbers between 1-9 in a range? 1 3 4 5 6 7 8 8 […]

This blog article answers a comment in this blog article: Identify missing values in two columns using excel formula Question: […]

Question: How do i return multiple results for one lookup value? I have tried to use multiple vlookups, with no […]

This article demonstrates how to use INDEX and MATCH functions to match multiple conditions and return multiple results. The Excel […]

This article demonstrates how to use INDEX and MATCH functions to lookup and return multiple results. The lookup value is […]

This article demonstrates formulas that let you perform lookups using two or more conditions. The image above shows two conditions […]

Question: I have a column "B" with a last name.. I have another columb with a date in it "C"... […]

This article demonstrates a formula that extracts values based on a condition and sorts the returned values based on values […]

This article demonstrates how to extract records/rows based on two conditions applied to two different columns, you can easily extend […]

The above image demonstrates a formula that adds values in three different columns into one column. Table of Contents Merge […]

The picture above shows how to merge two columns into one list using a formula. Table of Contents Merge two […]

This article demonstrates array formulas that search for cell values containing a search string and returns corresponding values on the […]

Question: I want to search two columns with two search strings? The strings can be anywhere in these two columns […]

This article demonstrates formulas that let you perform partial matches based on multiple strings and return those strings if all […]

This article demonstrates a formula that extracts a row or record from a data table using two strings as criteria. […]

Question: Can expand this equation set into more than two colums of data, say if I had a first, middle […]

This article demonstrates how to populate a drop down list with filtered values from an Excel defined Table. The animated […]

This article demonstrates formulas that rearrange values in a cell range to a single column. Table of Contents Rearrange cells […]

The picture above shows data presented in only one column (column B), this happens sometimes when you get an undesired […]

The array formula in cell B15 extracts dates from B4:B12 if it is not a duplicate item in the same […]

The image above shows a formula in cell C11 that extracts values from column D if the number in cell […]

This article demonstrates two different formulas, one for Excel 365 and one for earlier versions. Table of Contents Reverse a […]

RU asks: Can you please suggest if i want to find out the rows with fixed value in "First Name" […]

This article demonstrates array formulas that identify two search values in a row or in a sequence. The image above […]

This article explains different techniques that filter rows/records that contain a given text string in any of the cell values […]

I will in this article demonstrate how to search a table for a date based on a condition and then […]

Table of Contents Search values distributed horizontally and return the corresponding value Filter values distributed horizontally - Excel 365 1. […]

Geoff asks: Hi Oscar, I have a cross reference table we use for shift scheduling. The x-axis is comprised of […]

This article demonstrates how to extract multiple numbers based on a condition and return a sorted list from small to […]

This article demonstrates how to sort numbers from small to large using a condition or criteria, I will show how […]

This article demonstrates a formula that sorts text values based on character length, the Excel 365 dynamic array formula is […]

This article demonstrates ways to sort numbers from smallest to largest ignoring duplicate numbers. Table of Contents SMALL function with […]

Today I learned how to sort numbers from multiple cell ranges thanks to Sam Miller. It is surprisingly simple and easy. […]

Question: How do I sort a range alphabetically using excel array formula? Answer: Cell range $B$2:$E$5 contains text values in random […]

Andre asks:I am trying to list people with the highest scores based on certain criteria. My data: column A B […]

In this article, I will demonstrate two techniques for counting per row. The first example is simple and straightforward. The […]

This article demonstrates how to sort a data set by multipe columns using the SORTBY function, Ecel Table and Excel […]

Array formula in D5: =SMALL(IF(($B$3:$B$12<=$E$3)*($B$3:$B$12>=$E$2), $B$3:$B$12, "A"), ROWS($A$1:A1)) How to create an array formula Copy array formula Select cell D5 […]

Ralee asks in in this blog post: Sort values in parallel (array formula) If there is information in adjacent columns, […]

Table of Contents Sort text from two columns combined (array formula) How to create an array formula How to copy […]

The image above demonstrates a formula in cell D3 that sorts values based on character length, the value with the […]

The image above shows a table with two columns in cell range B3:C16, it contains random text values in column […]

This article describes a formula that sorts values arranged in a column from A to z by every other value. […]

Question: How do I divide values equally into groups (3 lists or less)? This post shows you two different approaches, […]

Mark G asks in Create a random playlist in excel: Can this example be modified to create a true round-robin […]

This article demonstrates how to extract multiple values based on a search value and display them sorted from A to […]

This post describes how to search filtered values in an Excel defined Table using a condition given in cell 12 and return […]

VLOOKUP and return multiple matches based on many criteria.

This article shows how to compare two nonadjacent cell ranges and extract values that exist only in one of the […]

## Functions in this article

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