title: How can I design a conditional SUM, to get a calendar monthly sum across various date ranges in VBA or Excel?
body: <p>I have a dataset that contains consumption of units across several date ranges.
  The length of the range varies: sometimes it coincides with a calendar month, other times it's all over the shop, i.e. starts on a random day of the month and ends at a random day of either the same or the next, or even 2-3 months later.</p>
  <p>My goal is to transform the data in such a way that I have the consumption broken down (and aggregated) across calendar months instead.
  For instance if I had ranges between:</p>
  <ol>
  <li>5 December 2020 and 14 February 2021,</li>
  <li>15 February 2021 and 9 March 2021,</li>
  <li>10 March and 30 April 2021,</li>
  </ol>
  <p>it would be transformed to a dataset that would have the unit values corresponding with the full calendar months of January, February and March 2021.
  For the purposes of this exercise, it can be assumed that consumption does not vary across days, i.e. each day corresponds with the same amount of consumption.</p>
  <p><strong>How could this be achieved with a VBA script?</strong></p>
  <p>I have explored the idea of using the worksheet function SUMIFS, but determining the date criteria (because it's a range, not a single date) seemed impossible.
  I have also thought of looping through the data with several IF conditions and adding up what I need, but then I'd run the risk of not picking up certain data.</p>