# Automatically changing the range of a SUM...

#### Aitch

##### Board Regular
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

### Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.

#### VBE313

##### Well-known Member
Assuming your data is A1:B10, In C1, enter

Code:
``=IF(A1<>"","Yes","")``

and fill it down. In D1, Enter
Code:
``=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

##### Board Regular
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!

Replies
7
Views
95
Replies
3
Views
214
Replies
1
Views
254
Replies
2
Views
187
Replies
1
Views
183

### Forum statistics

1,127,407
Messages
5,624,587
Members
416,036
Latest member
eloisa manzanarez

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back