Formula to count based on a date Range
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!
Best Answer

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!
Answers

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!

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

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?

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

A missing column would certainly break it! 🤣
Glad it's all fixed and working for you now though.
Help Article Resources
Categories
Check out the Formula Handbook template!