Is there a formula which can capture information from from a rolling 12-month period?

Options

I need to set up some cross-sheet formulas to capture how many machines we have installed over the past 12 months, broken down by each month - however I want this to be a rolling metric which only captures the information from the past 12 months - is this possible using a Smartsheet function, and if so please could you show an example as I am fairly new to formulas.


Many thanks!

Tags:

Answers

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Options

    You would need to incorporate the DATE function in the COUNTIFS similar to...


    =COUNTIFS({Date Range}, IFERROR(DATE(YEAR(TODAY()), MONTH(TODAY()) - 1, 1), DATE(YEAR(TODAY()) - 1, MONTH(TODAY()) + 11, 1)))


    The above will count for the previous month. To adjust it so that it will count for two months ago, yo would adjust the numbers following the MONTH functions (in bold) to be minus two and plus ten. Three months ago would be minus three and plus nine. So on and so forth.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!