# Won't Convert to Column Formula

Options

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!

• ✭✭✭✭✭✭
Options

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!

SMARTSHEET EXPERT CONSULTANT & PARTNER

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

W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35

Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

• ✭✭✭✭✭
Options

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!

• Options

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!

• ✭✭✭✭✭✭
Options

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!

SMARTSHEET EXPERT CONSULTANT & PARTNER

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

W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35

Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

• Options

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

• ✭✭✭✭✭✭
edited 02/17/23
Options

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!

SMARTSHEET EXPERT CONSULTANT & PARTNER

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

W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35

Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

• Options

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?

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!