|
Classic file types are no longer available for use as of January 2021. You can transition your classic files or download a PDF. Learn More

Workiva Support Center

ROWS

ROWS

  • Last updated on
Related to spreadsheet functions

Description

Use this function to determine the number of rows in a specified array or reference.

Syntax

ROWS(array)

Inputs

This function accepts the following argument:

Name Required Description Valid Input
array Yes The range or array to use for calculating the number of rows. A cell range, a formula that returns a cell range, or a reference to a cell containing a cell range.

Examples

Sample data

A B C D E
1 Fruit Price On Hand Promised Available
2 Apple 1.25 23 14 9
3 Banana 1.22 45 12 23
4 Mango 0.98 75 50 25
5 Peach 1.35 12 12 0

Sample formulas

Use case Formula Explanation and Result
Retrieve data from a specific row in a range. =INDEX(A1:B10, ROWS(A1:A5), 2)

This formula returns the value from the 5th row, 2nd column of the range A1:B10. 

For this data set, the result is 1.35.
Calculate the average of values in a row but only for a specified number of rows, which will change dynamically based on the rows selected. =AVERAGE(INDEX(B2:B100, 1):INDEX(B2:B100, ROWS(B2:B100)))

This formula works in the following manner:

  • INDEX(B2:B100, 1): This part of the formula returns the first cell in the range B2:B100, which is B2.
  • INDEX(B2:B100, ROWS(B2:B100)): This part of the formula returns the last cell in the range B2:B100, which is dynamically determined by the number of rows in the range.
  • AVERAGE(...): This function calculates the average of the values in the range defined by the two INDEX functions.

For this data set, the result is 1.2.
Create a running total formula that sums values across multiple rows, and the number of rows should be dynamic based on user input. =SUM(B2:INDEX(B2:B100, MIN(ROWS(B2:B100), D1)))

This formula works in the following manner:

  • B2:B100: This is the range of data you want to sum.
  • INDEX(B2:B100, MIN(ROWS(B2:B100), D1)): This part of the formula dynamically determines the end of the range based on the user input in cell D1. The MIN function ensures that the end row does not exceed the total number of rows in the range.
  • SUM(B2:INDEX(...)): This function sums the values from the beginning of the range to the dynamically determined end row.

For this data set, the result is 4.8.
Count the number of non-blank cells in a dynamic range where the number of rows may change. =COUNTA(A1:INDEX(A1:Z1, ROWS(A1:B1)))

This formula counts the number of non-blank cells in the first "n" rows based on the number of rows selected in A1:B1.

For this data set, the result will be 0, as there are no empty cells.

Notes

  • As with all array functions, ROWS dynamically populates results across adjacent cells, starting from the cell containing the ROWS function.

Tips

  • ROWS can be combined with other functions, including:
    • FILTER: To select specific rows from filtered data.
    • SORT: To rearrange rows.
    • UNIQUE: To extract unique values from selected rows.
    • SEQUENCE: To create dynamic row selections.
    • INDEX: For advanced row referencing.
    • MATCH: For dynamic row selection based on criteria.
    • TRANSPOSE: To reorient selected rows.
    • CHOOSEROWS: For more complex data selection.

Related functions

Here are the other array formula functions supported in Workiva spreadsheets. 

Was this article helpful?
0 out of 0 found this helpful
DescriptionUse this function to determine the number of rows in a specified array or reference.SyntaxROWS(array)Input...