2

In Google Sheets, how can I create an ARRAYFORMULA to generate sequential numbering in column B, only when adjacent C cell is not empty?

  • I want to start sequential numbering from B10,
  • If a cell in column C is blank, numbering in B cell be skipped, and then it continues from the next non-empty C cell.

Example:

Column B Column C
1 apple
2 mango
‎ ‎
3 orange
4 peach

I have found formulas for sequential numbering that ignore blank cells in the adjacent column, but these formulas need to be dragged down whenever new rows are added. I want to use ARRAYFORMULA so that it applies automatically to the entire range.

I tried using this formula generated by Claude:

=ARRAYFORMULA(IF(C10:C<>"", ROW(C10:C) - ROW(C10) + 1, ""))

This formula returns a blank in B cell when the adjacent C cell is empty, but it continues the numbering. So, in the table, it would show 4 - oranges , and , 5 - peach.

Improve this question

2 Answers 2

Reset to default
1

Here's one approach which you may adapt accordingly:

=map(C2:C,lambda(Σ,if(Σ="",,counta(C2:Σ))))

enter image description here

Alternative to try just in case those blank cells in Column_C are not true blank cells (which gives ISBLANK(<blank cell>)=false)

=map(C2:C,lambda(Σ,if(Σ="",,let(Σ,C2:Σ,counta(filter(Σ,Σ<>""))))))
Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

1

You can also try this formula:

=ArrayFormula(IFNA(MATCH(ROW(C2:C6),FILTER(ROW(C2:C6),C2:C6<>""),0),))

enter image description here

Here's the breakdown:

First, we create an Id by using the ROW Function: enter image description here

Next, we filter the Ids based on the condition that Column C is not empty: enter image description here

Finally, we use the MATCH Function to search for the Ids from the Filtered Ids to get the sequential number: enter image description here

If we combine all of this and add the IFNA Function to replace the #N/A Errors with a blank, we'll get the solution proposed above:

    =ArrayFormula(IFNA(MATCH(ROW(C2:C6),FILTER(ROW(C2:C6),C2:C6<>""),0),))
Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.