Incremental number increase with Countif and Offset when year criteria is met

1. Incremental number increase with Countif and Offset when year criteria is met

Hi everybody,
I am working on the following problem and seemed to get stuck.
My table contains the following information

 # Date Text 20/0001 01.01.2020 Sample text 20/0002 17.01.2020 Sample text 19/0001 22.12.2019 Sample text 19/0002 28.12.2019 Sample text 20/0003 22.12.2020 Sample text

In a nutshell, I am trying to build a incrementally increasing number in Column A based on the logic that:
If a date is within the same year, increase the number in column by 1.

Best regards,
Maxim

2. Re: Incremental number increase with Countif and Offset when year criteria is met

 A B C 1 # Date Text 2 20/0001 01.01.2020 Sample text 3 20/0002 17.01.2020 Sample text 4 19/0001 22.12.2019 Sample text 5 19/0002 28.12.2019 Sample text 6 20/0003 22.12.2020 Sample text

B2=IF(\$B2<>"",IF(SUMPRODUCT(--(RIGHT(\$B\$2:B2,2)+0=RIGHT(B2,2)+0)),RIGHT(B2,2)&"/"&"000"&SUMPRODUCT(--(RIGHT(\$B\$2:B2,2)+0=RIGHT(B2,2)+0)),""),"")

Copy down

3. Re: Incremental number increase with Countif and Offset when year criteria is met

=text(b2,"yy/")&text(countifs(\$b\$1:b2,">="&date(year(b2),1,1);\$b\$1:b2,"<"&date(year(b2)+1,1,1)),"0000")

4. Re: Incremental number increase with Countif and Offset when year criteria is met

Thank you Carcalla,
unfortunately, it seems that this solution does not work as anticipated.
Attachment 659620

5. Re: Incremental number increase with Countif and Offset when year criteria is met

Thank you BMV,
the approach generally works, but once I hit the last row there is no subsequent reference and hence the formula returns 00/0000.
Attachment 659621

6. Re: Incremental number increase with Countif and Offset when year criteria is met

MaximH, try this
=text(b2,"yy/;;;")&text(countifs(\$b\$1:b2,">="&date(year(b2),1,1);\$b\$1:b2,"<"&date(year(b2)+1,1,1)),"0000;;;")

7. Re: Incremental number increase with Countif and Offset when year criteria is met

attach file

There are currently 1 users browsing this thread. (0 members and 1 guests)