Excel ROWS Function
Microsoft Excel is a table made up of multiple rows and columns. Whatever we do in Excel, every time we work on cells. A cell (cell address) is defined as an intersection of a row and column. While working with Excel worksheets, there occur certain situations where we need to find the total number of rows for the range of cells we have selected. To counter these situations, Excel has an inbuilt ROWS function that allows the user to calculate the number of rows present in the selected range or an array quickly.
In this tutorial, we will cover all the aspects of the Rows function, including its definition, syntax, parameter, return value, and various examples.
What is Excel ROWS Function?
“The Excel ROWS function returns the count of rows for a given reference. For example, ROWS(B1:B4) returns 4 as an output, since the specified range B1:B4 in the array argument contains 4 rows.”
In simple terms, the Excel “ROWS” function counts the number of rows selected in the range of an array. Many often, Excel users confuse the ROWS function with the row function. But both are different. The ROW function only returns the row number for the selected cell; instead, the ROWS function accepts a range of cells or an array of rows as a parameter and returns the count of the number of rows in that array. This is also known as a referencing function to determine the count of rows in a specified array.
The ROWS function is a prevalent inbuilt Excel function that comes under the category of Lookup or Reference Function. This function lookups what the rows in an array/reference provided in the argument, and after computing, it returns the number of those rows as a result. Whenever you navigate and hover your mouse cursor towards this function in the Lookup category, you can see its definition in Excel. You can refer to the following screenshot to make things look clearer.
ROWS accept just one parameter, known as array, which can be a range or array. For example, if you specify an array {1;2;3}, it will return 3 as an output because this function will consider each constant as a row number.
Syntax
Parameters
- Array (required): This argument specifies an array of, or a reference to, a list of numbers from which the user want to find the count of rows.
Return
The Rows function in Excel returns the count of rows in a given reference.
Points to Remember
- The array argument can be a range or even a reference to a single or contiguous group of cells.
- The array argument can be a constant array or an array formed by another formula.
- If you are trying to count columns, refer to the Columns Function.
- If you are trying to fetch row numbers, refer to the Row Function.
- If you are trying to locate or lookup a row number, refer to the Match Function.
Examples:
Example 1: Use ROWS function to calculate number of rows from the set of numeric data values given in the below table.
| List of Numbers | |||
|---|---|---|---|
| 0 | 1 | 5 | 1 |
| 0 | 1 | 1 | |
| 1 | 0 | 0 | |
To find out the row number of the above range value follow the below given steps:
STEP 1: Add an empty row
The first step is to add a helper row below the ‘List of Numbers’. We will name the empty helper row as ‘Total Rows’.
It will look similar to the below image:
In this column, we will apply the Excel Rows function so to count the number of rows present in the list of numbers range.
STEP 2: Insert the formula
Move to the next column and start typing the formula. Starting with equals to(=), formula name and an open parenthesis. = ROWS (
NOTE: Make sure you type or select the ROWS function, and users often mistakenly type ROW.
Refer to the below image:
STEP 3: Add the reference parameter
As soon as you insert the formula, it will ask you to specify the argument i.e., the required input array for which you wish to count the number of rows.
So, we need to specify the range of cells in the array parameter. Put the range C5:E8 in the argument and close the parenthesis. Your formula will become, =ROWS (C5:E8)
Refer to the below image:
STEP 4: ROWS will return the result
Once you type the formula, press enter button to fetch the output since the range of cells has only 4 rows. Therefore the ideal output will be 4.
NOTE: The ROWS function doesn’t look out for column numbers.
Refer to the below image for the resulting output:
Example 2 – Using Excel ROWS function calculate the count of Rows for an Array Constant given below:
Array = {1;3;4;5;6}
The Excel ROWS function also be applied on array constants (something that numbers under curly brackets). We have used semi-colon instead of comma to separate the array constants because semi-colon converts them into individual array constants.
To calculate the count of row numbers for the above array constants, follow the below-given steps:
STEP 1: Add an empty row
The first step is to add a helper row below the ‘Array Constants’. We will name the empty helper row as ‘Total Rows’.
It will look similar to the below image:
In this column, we will apply the Excel Rows function so to count the number of rows present in the array.
STEP 2: Insert the formula
Move to the next column and start typing the formula. Starting with equals to(=), formula name and an open parenthesis. = ROWS (
Refer to the below image:
STEP 3: Add the reference parameter
As soon as you insert the formula, it will ask you to specify the argument i.e., the required input array for which you wish to count the number of rows.
So, we need to specify array constants in the array parameter. Inside the function use {2;4;6;7;8} as an argument and close the parenthesis. Your formula will become, =ROWS({2;4;6;7;8})
NOTE: Make sure to use semicolon as it will convert the values into individual rows.
Refer to the below image:
STEP 4: ROWS will return the result
Once you type the formula, press enter button to fetch the output. Since there are 5 array constants, therefore the ideal output will be 5.
Refer to the below image for the resulting output:
The ROWS function considers each array constant as a row number and returns the count of the values present in the specified array. For example, 2 specifies the 2nd row, 4 stands for 4rd, 6 stands for the 6th row, etc. The impressive thing to consider is the ROWS function does not include the rows in-between. Like 3rd and 5th, rows are not included in the array argument. We used a semicolon as a separator representing each element of an array as a unique entity.
