In Google Sheets, I’m looking for a way to use an ARRAYFORMULA to create sequential numbering in column B that only occurs when the corresponding cell in column C has a value. Here’s what I’m aiming for:
- The sequential counts should start from B10.
- If a cell in column C is empty, the sequence in column B should skip that entry and resume at the next filled cell.
For instance:
| Column B | Column C |
|---|---|
| 1 | apple |
| 2 | mango |
| 3 | orange |
| 4 | peach |
I’ve come across some formulas that facilitate sequential numbering while disregarding blanks in an adjacent column; however, those require manual dragging each time I add new rows. I want to achieve this with an ARRAYFORMULA that automatically covers the full range upon data entry.
I attempted to use the following formula suggested by a colleague:
=ARRAYFORMULA(IF(C10:C<>")", ROW(C10:C) - ROW(C10) + 1, ""))
This results in empty entries in column B whenever the adjacent cell in column C is empty, but the numbering persists, indicating entries such as 4 - orange, and 5 - peach.