How to restart count based on a criteria, but continue count if the same criteria is found below in-CodePudding

So I got the following scenario and my attempt gives a wrong result.

Would appreciate a helping hand here:

Here's a file with the data for test, should you feel like jumping in...

CodePudding user response：

There is a more straightforward approach. Delete everything (including the header) from Col B, then place the following in B1:

=ArrayFormula({"Task Nº"; IF(A2:A="",,COUNTIFS(A2:A,A2:A,ROW(A2:A),"<="&ROW(A2:A)))})

CodePudding user response：

Thanks to @Pankaj's direction and with a tweak, here is the solution:

={"Task Nº";ArrayFormula(IF(LEN(A2:A), SORT(ROW(A2:A),SORT(ROW(A2:A),A2:A,1),1)-MATCH(A2:A,SORT(A2:A),0),))}