- Code: Select all Expand viewCollapse view
`A B C`

1 Date Qty MonthlyTotal

2 1/1/2008 1

3 1/12/2008 3

4 1/22/2008 2 6

5 2/1/2008 5

6 2/18/2008 4 9

7 3/28/2008 3

8 3/28/2008 1 4

I need to write a formula to calculate the monthly sum (into Column C) of the Qty on the last day row present for that month. The below formula almost works, but I don't know how to make the Sum() operation dynamically look for the range of cells so it only adds up that month's Qty values (i.e. the B2 reference part needs to be dynamically changed to match that month's first row):

C4=IF(MONTH(A4)<>MONTH(A5); SUM(B2:B4); "")

(For simplicity, ignore the fact that the last row will always have a sum)