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!