Automatically changing the range of a SUM...

[Aitch]

I have a sheet similar to below:

Column A is the date, Column B has values for that day and Column C is the SUM of the values for that day - how do I make a formula that looks at the Date column and automatically defines the range for the SUM?

I've tried INDEX/MATCH to find the non-blank cells in Column A and use them for the range in SUM... but no luck!

Would appreciate some help!

1st Jan	20	SUM(B1:B4)
	21
	35
	44
2nd Jan	65	SUM(B5:B6)
	24
3rd Jan	11	SUM(B7:B10)
	47
	52
4th Jan

 

[VBE313]

Assuming your data is A1:B10, In C1, enter

=IF(A1<>"","Yes","")

and fill it down. In D1, Enter

=IF(C1="","",IF(IF(C1="Yes",SUM(OFFSET(INDIRECT(ADDRESS(ROW(),2)),0,0,IFERROR(MATCH("Yes",C2:C2123,0),1+SUM(,C2:C2123)))),"")=0,0,IF(C1="Yes",SUM(OFFSET(INDIRECT(ADDRESS(ROW(),2)),0,0,IFERROR(MATCH("Yes",C2:C2123,0),1+SUM(,C2:C2123)))),"")))

When you see ADDRESS(ROW(),2 the 2 stands for Column B. Drag this formula down too.

 

[Aitch]

Great thanks!

Is there a simpler way to do this, without needing an extra YES column?

If I don't copy the formula down the entire column - instead just copy/paste the cell when required?

I'm trying to use ROW in the SUM like this:

=SUM(INDIRECT("A"&ROW()) : A4)

This fixes the first range value, but I can't figure out how to get the second value automatically?

Thanks again!