Double-click cell B1, copy the formula =IF(ROW(A1:A5)>=3,A1:A5,0) to B1, press Ctrl + Shift + Enter, return 0 hold down the Alt key and press M And V separately, open the 'Evaluate Formula' window, press Enter to evaluate, then ROW (A1:A5) returns an array of 1 to 5 the operation steps, as shown in Figure 3:Ī. If you want to return all the row numbers of A1:A5. (III) A vertical reference to a range of cells and returns the arrayġ. Select cell B1, enter the formula =ROW(A5), press Enter, return to the Row_Num 5 of of A5 the operation steps are as shown in Figure 2: (II) Return the Row_Num of the specified row

Description: The formula =ROW() omits the argument Reference and returns the Row_Num of rows in which the formula is located. Select cell A1, enter the formula =ROW(), press Enter, return 1 select A1, move the mouse to the cell fill handle in the lower right corner of A1, and after the mouse becomes the bold black plus, press and hold the left button and drag down, the Row_Num of rows that are passed through are returned the operation process steps, as shown in Figure 1:Ģ.

(I) Returns the row number of the row in which the formula is locatedġ. The argument Array is an array or a reference to a range of cells and cannot be omitted. Rows function is used to return the number of rows referenced. You can see when pressing F9 or Row(A1:A9) as the criteria of If, as shown in the following example Reference cannot refer to multiple ranges at once.ī. If Reference is a vertical reference to a range of cells (such as A1:A9), the Row function returns the row number of all referenced cells as an array. The argument Reference is optional, if omitted, the Row_Num in which formula is located is returned. Row function is used to return the Row_Num of a cell or cell range referenced. I, Excel Row function and Rows function syntaxĪ. Alternatively, if you want to return a reference to a column, you can use Row(A:A) to return a reference to a row, you can use Row(1:1).

For example, the Row + Indirect + CountA combination returns all non-null cells as an array, SumProduct + Row + Mod combination adds even or odd rows. Row functions is often used in combination with Index, Small, Match, Indirect, OffSet, If, SumProduct, CountA, etc. The Rows function is used to return the number of rows of the array or cells that are referenced, there is only one parameter and cannot be omitted. If the argument is omitted, it will return the row number of the row in which the formula is located. The Row function is used to return the row number of a reference cell or a range of cells in Excel.

How to use row function excel