# 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

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.

#### 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
101
Replies
3
Views
223
Replies
1
Views
279
Replies
6
Views
101
Replies
6
Views
80

1,129,675
Messages
5,637,733
Members
416,981
Latest member
PLonchar

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