+ Reply to Thread
Results 1 to 7 of 7

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

  1. #1
    Registered User
    Join Date
    09-23-2013
    Location
    Germany
    MS-Off Ver
    365
    Posts
    14

    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.

    Thanks everyone for your help in advance.

    Best regards,
    Maxim

  2. #2
    Forum Expert
    Join Date
    09-25-2015
    Location
    Milan Italy
    MS-Off Ver
    office 365
    Posts
    1,781

    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. #3
    Forum Expert BMV's Avatar
    Join Date
    01-13-2020
    Location
    St-Petersburg, Russia
    MS-Off Ver
    2013-2016
    Posts
    1,329

    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. #4
    Registered User
    Join Date
    09-23-2013
    Location
    Germany
    MS-Off Ver
    365
    Posts
    14

    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. #5
    Registered User
    Join Date
    09-23-2013
    Location
    Germany
    MS-Off Ver
    365
    Posts
    14

    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. #6
    Forum Expert BMV's Avatar
    Join Date
    01-13-2020
    Location
    St-Petersburg, Russia
    MS-Off Ver
    2013-2016
    Posts
    1,329

    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. #7
    Forum Expert
    Join Date
    09-25-2015
    Location
    Milan Italy
    MS-Off Ver
    office 365
    Posts
    1,781

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

    attach file
    Attached Files Attached Files

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Incremental increase in date in formula
    By mgsc in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 12-16-2019, 10:15 AM
  2. [SOLVED] Incremental increase formula
    By allgeef in forum Excel Formulas & Functions
    Replies: 11
    Last Post: 05-09-2018, 10:57 PM
  3. [SOLVED] Formula for incremental increase of count
    By BRISBANEBOB in forum Excel General
    Replies: 3
    Last Post: 09-15-2016, 09:21 AM
  4. Need to calculate incremental increase
    By denk15 in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 03-25-2013, 08:03 AM
  5. Replies: 1
    Last Post: 05-08-2012, 09:31 PM
  6. countif. dates in a year (year is the criteria)
    By barrfly in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 01-18-2010, 03:33 PM
  7. Formula for incremental increase
    By alexrm in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 10-07-2005, 06:05 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1