# Calculate occurrences in last 12 months AND in current year

Options
✭✭✭

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

• ✭✭✭✭✭✭
edited 11/27/23 Answer ✓
Options

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

• Employee
Options

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

Join us at Smartsheet ENGAGE 2024 🎉
October 8 - 10, Seattle, WA | Register now

• ✭✭✭
Options

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:

• ✭✭✭✭✭✭
edited 11/27/23 Answer ✓
Options

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

• ✭✭✭
Options

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!