I have a list of items in column A. In column B I want to use an ARRAYFORMULA function that will bring the serial number of each item to appear - 1 next to the first item, 2 next to the second etc.
This formula works fine but breaks when there are blank rows:
=ARRAYFORMULA(IF(LEN(A2:A),ROW(A2:A)-1,""))
This formula works nice but I need to drag it which I don't want to do. This is why I want it as an ARRAYFORMULA (note that I fix the range to always start from A$1 so only the range changes in size as I drag it further down).
=IF(LEN(A2),COUNTA(A$1:A2),"")
What I need is basically something that will work like the 2nd formula but with an ARRAYFORMULA.
Here's a spreadsheet to make it clearer (col A is the list, col C is function 1 and col D is function 2): https://docs.google.com/spreadsheets/d/17qVGwvFJVrxdwkgmVQatH3Urpjl0_xB8EGqUsPVIwl8/edit?usp=sharing
In F2 I entered
=ArrayFormula(if(len(A2:A), countifs(A2:A, "<>", row(A2:A), "<="&row(A2:A)),))
See if that works for you?