Formula to count based on a date Range

Anastasia Blades

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!

Nick Korna

Hi @Anastasia Blades,

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!

Nick Korna

Hi @Anastasia Blades,

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!

Anastasia Blades

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

Nick Korna

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?

Anastasia Blades

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

Nick Korna

A missing column would certainly break it! 🤣

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