SUM dynamic range, excluding total row and including a stopping point

I need help with summing a dynamic range, but I need to exclude the total row at the bottom of the range and I want to include a start and stop point (beginning and end of a month). Sample.xlsx

I've attached a sample doc with date ranges. I've been playing around with the OFFSET formula and am able to sum the range dynamically, but can't get the formula to stop at the end of a month while excluding the total row at the bottom.

Essentially I want to sum data for a given month, doing so while adding new rows at the bottom of the range each day, then stopping when the given month ends (while excluding the total column altogether).

Thanks everyone!!

Originally Posted by AlKey

Since sum of column B is equal the total use this formula


=SUM(B:B)/2

This will sum the column, minus the total, but I need to sum individual months (so rows 1-31, 32-59, etc.) while continually adding a new row with data each day.

D2 Represents Month.
Formula
=SUMPRODUCT((MONTH($A$1:$A$64)=MONTH(D2))*($B$1:$B$64))

Originally Posted by kvsrinivasamurthy

D2 Represents Month.
Formula
=SUMPRODUCT((MONTH($A$1:$A$64)=MONTH(D2))*($B$1:$B$64))

This may be on the right track. Can you show how the formula would work for the month of March though? If I add another row (65) and enter the next date (3/6/2015), how would this be dynamically added for the March formula?

In this formula
=SUMPRODUCT((MONTH($A$1:$A$64)=MONTH(D2))*($B$1:$B$64))
change 64 as 65.

Beyond the data entered range also you want to include this ARRAY formula works
=SUM(IF(ISNUMBER($A$1:$A$164),(MONTH($A$1:$A$164)=MONTH(D2))*($B$1:$B$164),0))

Originally Posted by kvsrinivasamurthy

In this formula
=SUMPRODUCT((MONTH($A$1:$A$64)=MONTH(D2))*($B$1:$B$64))
change 64 as 65.

Beyond the data entered range also you want to include this ARRAY formula works
=SUM(IF(ISNUMBER($A$1:$A$164),(MONTH($A$1:$A$164)=MONTH(D2))*($B$1:$B$164),0))

Fantastic!! That did it, thank you!!