Calculate occurrences in last 12 months AND in current year

SOmalley1124

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

Zachary Hall

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!

Genevieve P.

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

SOmalley1124

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

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!

SOmalley1124

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