Report Comparing Year to Year
I am trying to create a report to compare if items received from year to year. I have sheets and want to know at this time last year how many items were received in comparison to the number received as of the current day. Is this possible?
Answers
-
Hi @sholmes
What I would do in this instance is use a separate "metric" sheet and create some cross-sheet formulas to generate your numbers.
For example, if you set up a Metric sheet like so:
Then you can have a Record a Date workflow change the date in Today's date cell every dat at a specific time (using a Condition Block to only update the one date in the row with "Today's Date" in the Time Frame column. The cell below, last year's date, removes 365 days from that date:
=Date1 - 365
See: Use Formulas to Perform Calculations With Dates
Then you can use these dates as a reference point in your formulas... either a COUNTIFS or a SUMIFS, depending on how your source sheet is set up.
For example:
=COUNTIFS({Item Column}, <> "", {Date Column}, >= DATE(2023, 01, 01), {Date Column}, <= Date@row)
or
=SUMIFS({Item Count Column}, {Date Column}, >= DATE(2023, 01, 01), {Date Column}, <= Date@row)
See: COUNTIFS Function / SUMIFS Function / DATE Function / Create cross sheet references to work with data in another sheet
Cheers,
Genevieve
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.5K Get Help
- 402 Global Discussions
- 213 Industry Talk
- 450 Announcements
- 4.7K Ideas & Feature Requests
- 141 Brandfolder
- 135 Just for fun
- 56 Community Job Board
- 454 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 296 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!