Count the number sequence same for same item but restart to 1 when enter new month-CodePudding

I want to get sequential number as in column AB as image attached below:

Sequence number in column AB would be my expected outcome but what I can do is just overall sequence number as in column AA. This is my formula in column AA: =IF(COUNTIF(Y$2:Y4,Y4)=1,MAX(AA$1:AA3) 1,VLOOKUP(Y4,Y$1:AA3,3,0))

Cannot think anymore how to restart sequence number to 1 when its a different month.

CodePudding user response：

=query({unique(filter(Y$2:Y,Z$2:Z=Z2)),sequence(rows(unique(filter(Y$2:Y,Z$2:Z=Z2))))},"select Col2 where Col1 = '"&Y2&"'")

CodePudding user response：

try:

=INDEX(IF(Y2:Y&Z2:Z="",,COUNTIF(Y2:Y&Z2:Z, Y2:Y&Z2:Z)))

CodePudding user response：

"how to restart sequence number to 1 when its a different month."
=if(month(Z3)=month(Z2),<continuation>,1)
That is assuming that in Z you have numbers formatted as mmm-dd rather than text.

If it's text then =if(left(Z3,3))=left(Z2,3)),<continuation>,1)