How to track yearly metrics wo updating my formulas each year?
Greetings, I'm curious is there a way I'm not aware of to easily track yearly metrics without having to go into each formula and update the year? Normally, I would create a formula that says count if one thing is met as well as if it's in "2024". However, I have to update my formulas at the start of each new year.
Is there a way to enter the current year once and have all formulas reflect that year? Below is an example of what I created today without the year added.
Answers
-
The way I've dealt with this is to add a helper column to the underlying datasheet that identifies if something is "Current Year", "Previous Year", or "Older" -
=IF(YEAR(Date@row)=YEAR(TODAY()),"Current Year",IF(YEAR(Date@row)=(YEAR(TODAY())-1),"Previous Year","Older"
Then You can write a COUNTIFS formula that includes that new helper column with "Current Year" as part of the solution.If this answer resolves your question, please help the Community by marking it as an accepted answer. I'd also be grateful for your response - "Insightful"or "Awesome" reactions are much appreciated. Thanks!
-
You would use YEAR(TODAY()).
=COUNTIFS({Range}, @cell = "Something", {Date Range}, IFERROR(YEAR(@cell), 0) = YEAR(TODAY()))
-
@SSParks -
Paul has the best answer.
I sometimes create a field in the Sheet Summary for CFY and PFY (current year/prior year). It can be hard coded or a formula. You can then refer to it in your formulas. You refer to sheet summary fields like this :
CFY# or [Current FY]#
Where "CFY" is the name of the field and "#" replaces the row number. Same rules as regular column labels (square brackets for spaces, numbers, etc.).
Hope this helps!
-
Where you are currently writing 2024 you can write YEAR(TODAY())
That will return the current year.
Alternatively, you can also utilize the sheet summary and create a field called "Current Year" and the formula =YEAR(TODAY()) then you can refer to that field in your formula using: [Current Year]#
-
I need a little more info. If Im trying to count how many surveys were done at Fichtner Park in this year, and I added the year today formula in a cell, where to I add to the formula below to have this pull from "this year"? Am I referencing the cell I put the year today formula in or am I just adding that piece to my formula below?
=COUNTIF({BlueBird Monitoring Range 2}, "Fichtner Park")
-
It would look more like this (doesn't need to be in a cell):
=COUNTIF({BlueBird Monitoring Range 2}, "Fichtner Park", {Date Range}, IFERROR(YEAR(@cell), 0) = YEAR(TODAY()))
-
What am I missing still? Getting incorrect argument still. I have the YEAR column pull the year (from the Date column) and thats my range "{BlueBird Monitoring Range 4}"?
=COUNTIF({BlueBird Monitoring Range 2}, "Fichtner Park", {BlueBird Monitoring Range 4}, IFERROR(YEAR(@cell), 0) = YEAR(TODAY()))
-
Thank you to everyone who took the time to respond!
Below worked with a Current Year Column and the year in cell 1
=COUNTIFS({BlueBird Monitoring Range 2}, "Fichtner Park", {BlueBird Monitoring Range 4}, [Current Year]1)
This also worked
=COUNTIFS({BlueBird Monitoring Range 2}, "Fichtner Park", {BlueBird Monitoring Range 4}, YEAR(TODAY()))
So at the end of 2024, what do I do where I can keep my 24 data so I can compare it to my 25 data? Would I need new formulas for the 24 bc I assume the formula numbers would all change on Jan 1 since the year changed? Or would I keep a column for 24 with formulas and a column for 25 with year today?
Am I updating or changing formulas at the end of the year no matter what?
-
This was very interesting thank you for sharing. I was able to get a demo to work, but am I correct on January 1, my 2024 data would then turn to previous year with 25 being current year. How could you tweak this to reflect the actual years if you wanted to compare data from 24, 23, 22, 21, 20, etc (vs current/previous- bc what happens after 5 years?) Thanks for any info!
-
Thank you to everyone who took the time to respond!
Below worked with a Current Year Column and the year in cell 1
=COUNTIFS({BlueBird Monitoring Range 2}, "Fichtner Park", {BlueBird Monitoring Range 4}, [Current Year]1)
This also worked
=COUNTIFS({BlueBird Monitoring Range 2}, "Fichtner Park", {BlueBird Monitoring Range 4}, YEAR(TODAY()))
So at the end of 2024, what do I do where I can keep my 24 data so I can compare it to my 25 data? Would I need new formulas for the 24 bc I assume the formula numbers would all change on Jan 1 since the year changed? Or would I keep a column for 24 with formulas and a column for 25 with year today?
Am I updating or changing formulas at the end of the year no matter what?
-
The formula I provided ignored the Year column and evaluated the date.
But yes. Either way you are going to have to update formulas every year. My suggestion would be to go ahead and create the formulas for multiple years and just let them populate zero for now so that you don't have to fiddle with the sheet every year. Since it is something as simple as copy/paste/tweak, you can easily go out quite a few years.
=COUNTIFS({BlueBird Monitoring Range 2}, "Fichtner Park", {BlueBird Monitoring Range 4}, 2024)
=COUNTIFS({BlueBird Monitoring Range 2}, "Fichtner Park", {BlueBird Monitoring Range 4}, 2025)
=COUNTIFS({BlueBird Monitoring Range 2}, "Fichtner Park", {BlueBird Monitoring Range 4}, 2026)
so on and so forth…
-
Thank you for the information and clarification. I manage about 50 different Smartsheet grids for people at work and was just curious if I was missing something to avoid having to update formulas every year.
-
Yeah. In that case I would suggest hard-coding the year and pre-populating multiple columns / rows (depending on your structure) for future years as placeholders so that you don't have to. Using copy/paste, you can very quickly populate formulas for 10+ years. In the world of technology, if you are still using the same exact system in 10 years, either you had an absolute genius who could also predict the future build your solution, or your solution is outdated and need of a revamp anyway.
Help Article Resources
Categories
Check out the Formula Handbook template!