# 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…

Tags:

• ✭✭✭✭✭
edited 08/12/24

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

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!