Counting number of events (lines) within a time range
Hi,
I looked at the past discussion but didn't find the answer.
My sheet has a row for each events happening with the precise dates (ex: 8-Aug-2024). I would like to count any event that happened within a time frame (march 1rst to march 31rst of the next year).
I tried various formulas but no luck at all…
Answers
-
Make a column to the right of Date Realisee column, use the following formula
=iferror(if(AND([Date prevue]@row>=DATE(2025,03,01),[Date realisee]@row<=DATE(2025,03,31),1,0),0)
Make this formula the column formula for this new column
In your Sheet Summary, create a metric and use the following formula
=COUNTIF([New Column To The Right Of Date realisee]:[New Column To The Right Of Date realisee],1)
You can then make one metric for every month of the next year by changing the values within the DATE functions.
Let me know if this did not help you or if I misunderstood.
If you found this comment useful, please let me know by clicking one of the buttons below: Awesome, Insightful, Upvote, or Accepted Answer. Your feedback will assist others looking for the same information and also help me out.
Thanks!
Nick Stafford
-
Salut Alex Brioso,
I came up with the below solution, I hope it helps:
I have a mock sheet with the below data and columns:
I came up with two options:
Option one: is the [All Activities] column where I have the below formula:=IFERROR(JOIN(COLLECT([Theme Activity]:[Theme Activity], [Expected Date]:[Expected Date], >=[Start Date]@row, [Expected Date]:[Expected Date], <=[End Date]@row), ","), "--")
This formula will JOIN all of the activities within the [Start Date] and [End Date] columns separated by a ","Option Two: is the [Count of Activites] column where I have the below formula:
=IFERROR(COUNTIFS([Expected Date]:[Expected Date], >=[Start Date]@row, [Expected Date]:[Expected Date], <=[End Date]@row), "--")
This formula will count the number of activities that are within the [Start Date] and [End Date] column
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.1K Get Help
- 349 Global Discussions
- 199 Industry Talk
- 427 Announcements
- 4.4K Ideas & Feature Requests
- 133 Brandfolder
- 127 Just for fun
- 127 Community Job Board
- 455 Show & Tell
- 28 Member Spotlight
- 1 SmartStories
- 282 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!