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?
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?
Last Comment
ASKER CERTIFIED SOLUTION
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.
Karen Falandays
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
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.
A sample file would be helpful.
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
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
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.
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.
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
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
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.
If you want the sum at the top of the range for each week, you could copy down this formula:
sum by weeks.xlsx
=IF(E5=E4,"",SUMIF(E:E,E5,C:C))
sum by weeks.xlsx
ASKER
Thanks! Yes, that block with the mix of dates... thanks for catching that!
SOLUTION
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.
Rob,
Thanks for catching my goof.
Brad
Thanks for catching my goof.
Brad
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
TRUSTED BY
