I want to count the number of consecutive times the same value appears until the given row, until another value appears then start counting from zero again. And I want to use an
arrayformula so I don’t have to copy it down all rows.
Input Count Foo 1 Foo 2 Foo 3 Bar Bar Foo 1 Foo 2
A way that works is something like this if you copy it all the way down:
=IF( A2="Foo", 1, B1+1 )
The problem is a formula like this doesn’t work for
arrayformula, because you can’t reference the preceding row, because this gives you an error for circular dependencies.
I think some combination of
countif might work but can‘t figure out how.
EDIT: I tried to use
mmult as suggested but that doesn’t seem to be able to solve the problem, because I can only count row by row. I can count all the rows with a certain value like this:
=ArrayFormula(MMULT(N( ROW(A2:A)>=TRANSPOSE(ROW( A2:A))), N(A2:A="Foo")) )
but I can’t reset the counter each time a different input appears.