# Excel - write a formula that sums till it hits a border line? Or change of text in a column?

I have a spreadsheet that has transactions on it.  Each line has 2 columns -  an amount and the week end

Each week can have different number of transactions.

I've gotten into putting a solid horizontal line between weeks.

Is there a way to sum a column that is between 2 solid lines?  Or sum the cells with the same week end?  I'd like to not have to edit the formula each week.  Just cut and paste on the first line of the new week.  so 'sum this row, with date x and each row after till the date changes'.

So how to sum on a varlable number of rows?

Ideas?
Microsoft OfficeMicrosoft Excel

Last Comment
byundt
byundt

THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
Sounds like you want to use Group and Total/Subtotals. Take a look at the ribbon in the Data>Outline grouping. You may need to restructure your data so that it has one row for each unique value and column headings for each field and then decide which field you want to group on
Rob Henson

Converting your data to a Table rather than standard list and then you can use a Pivot Table to summarise by the Week Ending Dates.

A sample file would be helpful.

byundt - I'm trying to understand that formula.

Rob - sorry, trying to keep it simple : )

writing a formula / learning some new commands is about the level of challenge I am looking for. Not looking to write macros,learn pivot tables.  I've made it this far without those.  This is more a'cute' thing and been tweaking the formula manually each week for the number of rows involved.

In putting together this sample file, I realized what I am looking for is subtotals?  On change in column E, sum column d?

Why reinvent the wheel?  But is there a way to write a formula to do the subtotal math?

sample.xlsx
Rob Henson

Pivot Tables are surprisingly simple!

See attached.

Converted the data to a Table and inserted a pivot table based on that table. Add data to the bottom of the table and the data range will automatically expand, right click in the Pivot Table and choose Refresh to update the summary figures.
sample (1).xlsx

BTW, filling all cells with white is just overkill for making the sheet look better, on the view menu just select the option to hide Grid lines (deselect option to show Grid lines). Excessive formatting can create file size bloat.
Rob Henson

Byundt's formula doesn't work with the layout you have with the subtotal at the top of each week. It would work if you had the summary at the bottom of each week:

In C5:
=IF(E5=E6,"",SUMIF(E\$5:E5,E5,D\$5:D5))

This looks at the dates in E5 and E6, if they are the same then return "" Else sum the values in column D where date in column E equals E5. The first reference to E5 is locked by the \$ so when it is copied down the column the range grows but always starts at row 5
Rob Henson

Also just noticed in your data, rows 55 to 70 have a mix of dates. With any formula or Subtotal function this would have to be resolved so that the items are in sequence. Pivot Table doesn't need that.
byundt

If you want the sum at the top of the range for each week, you could copy down this formula:
``````=IF(E5=E4,"",SUMIF(E:E,E5,C:C))
``````

sum by weeks.xlsx

Thanks!  Yes, that block with the mix of dates... thanks for catching that!

SOLUTION
Rob Henson

THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
byundt

Rob,
Thanks for catching my goof.