Report Comparing Year to Year

sholmes

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?

Genevieve P.

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