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