I am trying to count sequentially depends on the value using ArrayFormula to avoid manually dragging the column, I want to autofill everytime I add a value in Column A

in Column B - the ArrayFormula does not work

=ARRAYFORMULA(IF(I$1:I<>"", COUNTIF(I$1:I1, I1:I), ""))

in Column C - I tried to do it using

=COUNTIF(I$1:I1, I1:I)

A	B	C
A1	1	1
A1	1	2
A1	1	3
B1	0	1
B1	0	2
A2	0	1
A2	0	2

I am trying to count sequentially depends on the value using ArrayFormula to avoid manually dragging the column, I want to autofill everytime I add a value in Column A

in Column B - the ArrayFormula does not work

=ARRAYFORMULA(IF(I$1:I<>"", COUNTIF(I$1:I1, I1:I), ""))

in Column C - I tried to do it using

=COUNTIF(I$1:I1, I1:I)

A	B	C
A1	1	1
A1	1	2
A1	1	3
B1	0	1
B1	0	2
A2	0	1
A2	0	2

I tried to replicate the result in Column C using ArrayFormula but i couldn't do it.

Thank you.

• google-sheets
• google-sheets-formula

Share Improve this question Follow asked Nov 19, 2024 at 0:10 mj mjmj mj 1744 bronze badges 1

• Use a descriptive title please. See How to Ask – TheMaster Commented Nov 19, 2024 at 6:04

Add a comment  | 

1 Answer 1

Sorted by: Reset to default 1

Use the following formula in row 1 of an empty column:

=ARRAYFORMULA(IF(I:I="",,COUNTIFS(I:I,I:I,ROW(I:I),"<="&ROW(I:I))))

You could also do it using MAP:

=MAP(I:I,LAMBDA(_,IF(_="",,COUNTIF(I1:_,_))))