Won't Convert to Column Formula

dan.purcell

Trying to use a formula for autonumbering that will start over the fill number every year. Formula works well in a cell, but I can't get it to convert to a column formula

=IFERROR(RIGHT(YEAR(Date@row), 2) + RIGHT(COUNTIF(Date1:Date@row, YEAR(@cell) = YEAR(Date@row)) + 10000, 3), "")

Error message comes up that the column formula syntax isn't quite right, see our help article for more information

Any help would be appreciated!

Andrée Starå

Hi @dan.purcell

I hope you're well and safe!

It doesn't work because you're referencing a specific cell.

=IFERROR(RIGHT(YEAR(Date@row), 2) + RIGHT(COUNTIF(Date1:Date@row, YEAR(@cell) = YEAR(Date@row)) + 10000, 3), "")

I hope that helps!

Be safe, and have a fantastic week!

Best,

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

✅Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up, Awesome, or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

Victoria_Indimar

Hi Dan! If your only issue is getting it to convert to column formula, I think it's because of this section of the formula: COUNTIF(Date1:Date@row, YEAR(@cell)

It should work if you change your range to the full column: COUNTIF(Date:Date, YEAR(@cell)

Let me know if that helps!

dan.purcell

Thank you Andree & Victoria. Unfortunately changing the section of the formula to COUNTIF(Date:Date, YEAR(@cell) made the formula #UNPARSEABLE. Is there anything else I can try to convert this formula into a column formula? Is there a better formula I can use? Thank you for your help!

Andrée Starå

@dan.purcell

Happy to help!

Did you try this?

=IFERROR(RIGHT(YEAR(Date@row), 2) + RIGHT(COUNTIF(Date:Date@row, YEAR(@cell) = YEAR(Date@row)) + 10000, 3), "")

Did that work?

✅Remember! Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up/Awesome or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

dan.purcell

Andree, that formula did not work. #UNPARESEABLE response in cell. Thank you for your help!

Andrée Starå

@dan.purcell

I forgot to remove one part. Sorry about that!

This should work now.

=IFERROR(RIGHT(YEAR(Date@row), 2) + RIGHT(COUNTIF(Date@row, YEAR(@cell) = YEAR(Date@row)) + 10000, 3), "")

Did that work?

✅Remember! Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up/Awesome or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

dan.purcell

Thanks Andree!! It can be converted to a column formula now, but it won't count? My goal is to create an autonumbering system in the primary column of the sheet that counts the previous entries with the same "year" in the date column. So when a new year starts, the prefix will change. Am I heading down the right path with this formula?