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 95 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 […]
This article demonstrates formulas that extract differences between two given lists. The first formula in cell B11 extracts values from […]
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 […]
This webpage demonstrates formulas that merge three non-adjacent cell ranges. What's on this webpage Extract a list of duplicates from […]
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 formula decodes a URL-encoded string, replacing specific percentage symbol (%) and a hexadecimal number with non-alphanumeric characters. Excel 365 […]
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 signs
Use 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 Oscar
You can contact me through this contact form