Get current Row number inside ArrayFormula

Question

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?

score 30 · Accepted Answer · answered Feb 23 '12 at 12:09

30

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.

answered Feb 23 '12 at 12:09

Warwick

2

Thanks, this makes sense now! I wanted it to fill the sheet and expand when rows are added, so I used: `=ARRAYFORMULA(ROW(A2:A))` (starting under the header row) – rymo Feb 24 '12 at 18:20
If I need to be certain the numbers returned match the actual sheet row numbers they are on, but don't know which row the formula is starting on, is there a simpler method than this? `=ARRAYFORMULA(ROW(INDIRECT(CONCATENATE("A",ROW(),":A"))))` – rymo Feb 24 '12 at 18:31

score 19 · Answer 2 · edited Jul 07 '12 at 19:15

19

The following may be a little simpler:

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

or

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

edited Jul 07 '12 at 19:15

Bo Persson

answered Jul 07 '12 at 02:29

Yogi Anand

score 0 · Answer 3 · edited May 31 '22 at 08:11

0

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

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

edited May 31 '22 at 08:11

Tyler2P

answered May 29 '22 at 20:43

Manack

score 0 · Answer 4 · answered Jul 11 '23 at 08:14

0

Put this on the 1st Column header

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

answered Jul 11 '23 at 08:14

I have an error with this formula: "Circular dependency detected. To resolve with iterative calculation, see File > Settings." – Александр Ермолин Jul 11 '23 at 08:41
Go to File > Settings > Calculation Tab > Iterative calculation – Lyndon Broz Tonelete Jul 12 '23 at 00:28

4 Answers4