Moving Month to date Formula or Macro

[chrisscauk]

Hey,

So we have a daily energy sheet which picks up readings from other spreadsheets to calculate production vs energy on site.

We want to calculate the sum of tonnage produced month to date - the current day as this leaves a negative number.


So in column A we have the all the dates by day. The title start at Row 6 with data below and line 7 frozen.

[TABLE="class: grid, width: 500"]
<TBODY>[TR]
[TD]Date</SPAN>[/TD]
[TD]PM1 tonnage</SPAN>[/TD]
[/TR]
[TR]
[TD][TABLE="width: 48"]
<COLGROUP><COL style="WIDTH: 48pt" width=64><TBODY>[TR]
[TD="class: xl66, width: 64, bgcolor: transparent"]01-Jun-13[/TD]
[/TR]
</TBODY>[/TABLE]
[/TD]
[TD][TABLE="width: 48"]
<COLGROUP><COL style="WIDTH: 48pt" width=64><TBODY>[TR]
[TD="class: xl66, width: 64, bgcolor: transparent"]70.17[/TD]
[/TR]
</TBODY>[/TABLE]
[/TD]
[/TR]
</TBODY>[/TABLE]


So for example if it was 20th June 2013 today I need a formula that will recognise the month is June 2013 and only sum the months tonnage from the 1st June 2013 to the 20th June 2013, but then tomorrow it will be from the 1st June 2013 up to 21st June 2013. Then when the month changes to July it would need to recognise that the month has changed and this will take up the entire column from Row 7.

So in turn it needs to recognise the current month and sum from the first of the current month up to the current Date. A formula would be preferred but a macro would do.

 

[etaf]

pivot table any use ?

=SUMPRODUCT((MONTH(A7:A100)=7)*(B7:B100))
or instead of 7 you could use today()
=SUMPRODUCT((MONTH(A7:A100)=MONTH(TODAY()))*(B7:B100))
but that does not take into account the year