CODEWITHSUNDEEP
google-sheets
rymo
rymo

Reputation: 3464

Get current Row number inside ArrayFormula

In a Google Docs Spreadsheet, I would expect this formula:

=ARRAYFORMULA(ROW())

to fill the column like:

[   1]
[   2]
[   3]
[   4]
  ...

but instead it stops at 1. What is happening here? Is there another way to get the current row number in an arrayformula?

Upvotes: 27

Views: 67209

Answers (4)

Lyndon Broz Tonelete
Lyndon Broz Tonelete

Reputation: 51

Put this on the 1st Column header

=arrayformula({"NO.";IFERROR(IF(B2:B<>"",ROW(INDIRECT("A"&ROW()&":A")),""))})

If you have an error saying:

"Circular dependency detected. To resolve with iterative calculation, see File > Settings."

Go to File > Settings > Calculation Tab > Iterative calculation

Upvotes: 0

Manack
Manack

Reputation: 1

This works when you want row number limited to not-empty row

=ARRAYFORMULA(filter(ROW(INDIRECT(CONCATENATE("A";ROW();":A")));A2:A<>""))

Upvotes: 0

Yogi Anand
Yogi Anand

Reputation: 544

The following may be a little simpler:

=arrayformula(ROW(INDIRECT("A"&ROW()&":A")))

or

=index(ROW(INDIRECT("A"&ROW()&":A")))

Upvotes: 19

Warwick
Warwick

Reputation: 1230

You need to specify a cell-range argument for ROW() in order to have more than one value.

Try it this way:

=ARRAYFORMULA(ROW(A1:A10))

This will fill a column with row numbers from 1 to 10.

Upvotes: 30

Related Questions