Avatar of Adam Rabinow
Adam RabinowFlag for United States of America

asked on 

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

Avatar of undefined
Last Comment
byundt
ASKER CERTIFIED SOLUTION
Avatar of byundt
byundt
Flag of United States of America image

Blurred text
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.
See Pricing Options
Start Free Trial
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
Avatar of Rob Henson
Rob Henson
Flag of United Kingdom of Great Britain and Northern Ireland image

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.
Avatar of Adam Rabinow
Adam Rabinow
Flag of United States of America image

ASKER

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
Avatar of Rob Henson
Rob Henson
Flag of United Kingdom of Great Britain and Northern Ireland image

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.
Avatar of Rob Henson
Rob Henson
Flag of United Kingdom of Great Britain and Northern Ireland image

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
Avatar of Rob Henson
Rob Henson
Flag of United Kingdom of Great Britain and Northern Ireland image

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.
Avatar of byundt
byundt
Flag of United States of America image

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

Open in new window


sum by weeks.xlsx
Avatar of Adam Rabinow
Adam Rabinow
Flag of United States of America image

ASKER

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


SOLUTION
Avatar of Rob Henson
Rob Henson
Flag of United Kingdom of Great Britain and Northern Ireland image

Blurred text
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.
Avatar of byundt
byundt
Flag of United States of America image

Rob,
Thanks for catching my goof.

Brad
Microsoft Excel
Microsoft Excel

Microsoft Excel topics include formulas, formatting, VBA macros and user-defined functions, and everything else related to the spreadsheet user interface, including error messages.

144K
Questions
--
Followers
--
Top Experts
Get a personalized solution from industry experts
Ask the experts
Read over 600 more reviews

TRUSTED BY

IBM logoIntel logoMicrosoft logoUbisoft logoSAP logo
Qualcomm logoCitrix Systems logoWorkday logoErnst & Young logo
High performer badgeUsers love us badge
LinkedIn logoFacebook logoX logoInstagram logoTikTok logoYouTube logo