Automatically changing the range of a SUM...

Aitch

Board Regular
Joined
Jan 27, 2019
Messages
117
Office Version
  1. 2010
Platform
  1. Windows
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 20SUM(B1:B4)
21
35
44
2nd Jan65SUM(B5:B6)
24
3rd Jan11SUM(B7:B10)
47
52
4th Jan
 

Some videos you may like

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
Joined
Mar 22, 2019
Messages
683
Office Version
  1. 365
Platform
  1. Windows
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.




Snag_4d02728f.png
 

Aitch

Board Regular
Joined
Jan 27, 2019
Messages
117
Office Version
  1. 2010
Platform
  1. Windows
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!
 

Watch MrExcel Video

Forum statistics

Threads
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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top