Calculate occurrences in last 12 months AND in current year

Hello!

I am working on a dashboard that is focused on our tour program. I need a formula/function to show the amount of tours in the last 12 months and the amount of tours in the current year. Any help with this is greatly appreciated!

Shannon

Best Answer

  • Zachary Hall
    Zachary Hall ✭✭✭✭✭✭
    edited 11/27/23 Answer ✓

    Hi @SOmalley1124,

    On a sheet summary you could use the following formulas:

    • =COUNTIF([Tour Date]:[Tour Date],>TODAY(-365))
    • =COUNTIF([Tour Date]:[Tour Date], >DATE(YEAR(TODAY()), 1, 1))

    Hope this helps!

    Best,

    Zach Hall

    Training Delivery Manager / Charter Communications

Answers

  • Genevieve P.
    Genevieve P. Employee Admin

    Hey @SOmalley1124

    I'm sure the Community would love to help! However it would be useful if we could see a screen capture of your source sheet (blocking out sensitive data). The structure of your source sheet will determine the structure of the formula.

    For example, do you have a Date column that records the tour, or are there two dates? (Start and End). If there are two, but the tour crosses over a year, do you want that tour counted or not? Is each unique tour recorded on its own unique row, or are there duplicate rows?

    Thanks,

    Genevieve

  • Hi Genevieve!

    I have a tour database sheet that has one column showing the date of the tour. Each unique tour is recorded on its own row. Here is a snip of what it looks like:


  • Zachary Hall
    Zachary Hall ✭✭✭✭✭✭
    edited 11/27/23 Answer ✓

    Hi @SOmalley1124,

    On a sheet summary you could use the following formulas:

    • =COUNTIF([Tour Date]:[Tour Date],>TODAY(-365))
    • =COUNTIF([Tour Date]:[Tour Date], >DATE(YEAR(TODAY()), 1, 1))

    Hope this helps!

    Best,

    Zach Hall

    Training Delivery Manager / Charter Communications

  • Thank you Zach! I always forget about the amazing sheet summary option!!!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!