# Formula to count based on a date Range

Options

Im trying to write a formula that counts each month based on a date range so that a number is attached to each month within the date range.

ie, If an activity starts 04/01/24 and ends 08/01/24, I need to add "1" to each month in Between so the formula would reflect activites per month between start and end date. Attached is my data sheet and i am trying to populate the Sum column.

Thank you to anyone in advance who can help!

• ✭✭✭✭✭✭
Options

I would suggest making a couple of changes to your sheet layout - the Month Number will need to be a number rather than text. If you have anything which overruns from one year to the next, I would add in a Year column as well so you end up with something along these lines (the column positions can be shifted as desired, they aren't important):

The Sum column formula is:

=COUNTIFS([Start Date]:[Start Date], AND(IFERROR(MONTH(@cell), 0) <= [Month Number]@row, IFERROR(YEAR(@cell), 0) <= Year@row), [End Date]:[End Date], AND(IFERROR(MONTH(@cell), 0) >= [Month Number]@row, IFERROR(YEAR(@cell), 0) >= Year@row))

Hope this helps, but if I've misunderstood something or if you've any problems/questions then let us know!

• ✭✭✭✭✭✭
Options

I would suggest making a couple of changes to your sheet layout - the Month Number will need to be a number rather than text. If you have anything which overruns from one year to the next, I would add in a Year column as well so you end up with something along these lines (the column positions can be shifted as desired, they aren't important):

The Sum column formula is:

=COUNTIFS([Start Date]:[Start Date], AND(IFERROR(MONTH(@cell), 0) <= [Month Number]@row, IFERROR(YEAR(@cell), 0) <= Year@row), [End Date]:[End Date], AND(IFERROR(MONTH(@cell), 0) >= [Month Number]@row, IFERROR(YEAR(@cell), 0) >= Year@row))

Hope this helps, but if I've misunderstood something or if you've any problems/questions then let us know!

• Options

hi @Nick Korna Thank you for the response. I made the changes to my sheet, but that formula is returning as unparseable.

• ✭✭✭✭✭✭
Options

If your column headers are the same etc. then it shouldn't be giving you any issues. Can you paste a screenshot so we can possibly identify any problems?

• Options

Thank you @Nick Korna I double checked and realized i was missing a column. THIS WORKS!!! THANK YOU SO MUCH!

• ✭✭✭✭✭✭
Options

A missing column would certainly break it! 🤣

Glad it's all fixed and working for you now though.

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!