I have a column in a spreadsheet, where various charges are added and need to be subtotalled. The problem is that each section varies in length so the positions where the subtotals need to be moves around.
I have a column in a spreadsheet, where various charges are added and need to be subtotalled. The problem is that each section varies in length so the positions where the subtotals need to be moves around.
Welcome to the forum.
Are you still using Excel 2003?
There are instructions at the top of the page explaining how to attach your sample workbook.
Ali
Enthusiastic self-taught user of MS Excel who's always learning!
Don't forget to say "thank you" in your thread to anyone who has offered you help.
You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
Forum Rules (updated August 2023): please read them here.
Hi I am using Excel 2016. This is a reduced size version of the spreadsheet. Apart from the subtotals, it works OK, although I think there are probably better ways of constructing it.
Essentially, there are 4 grades of staff, A to D, each with a different hourly rate. Pieces of work are timed in either hours and minutes, or units –( a unit being 6 mins, .1 of an hour).
Data is input with column C being text description, column H being the Grade (A-D), and either the number of units or the hours & minutes. Column L just turns this into the total number of units.
The relevant hourly rates for the Grades A-D are in cells M1, N1, O1 and P1, named RateA, RateB, RateC and RateD.
Column D calculates the charge – it checks for input in column A. If there is something there (we use a * or a number), it takes the number of units of time from column L and multiplies by the relevant rate to get the amount to charge.
When there is something is column A, columns E and F also populate – F copies the charge in D and E calculate the VAT.
What I would like to do is to automate the subtotalling, in the example cells D8. D12, D17 and D20. The trouble is that each section is dynamic, and will move about as they have anything from 1 to 6 items to be totalled, and there will not just be four sections as in the sample, there may be 20 or 30. I can’t figure out any easy way to do this, so we do it manually as in the sample.
We could input a code in column A say T2 to total previous 2 rows, T3 for previous three rows etc. I am pretty rusty on Excel, and don’t want to use macros if at all possible.
Please change your user profile from Excel 2003 to Excel 2016. Thanks.
This recent thread asked the same question: https://www.excelforum.com/excel-for...l-appears.html
The second half of it in particular may help you.
Last edited by AliGW; 10-02-2021 at 05:10 PM.
Thank you for your reply, and I have changed my profile. The above doesn't work in my example, as there is already a formula in the cell where the subtotal would need to appear, and the 'empty' sheet prior to input, has no subtotals defined at all. They could be required to start at approx row 25 anywhere through to row 200. I need a way of putting something in a different column to 'trigger' the cell to subtotal the previous batch of input rather than the normal hours*rate cost calculation.
I have just had a closer look at that thread you suggested, and it does help - I think I can sort it from here. Many thanks for your help.
Just shout if you need any further help.
If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.
Also, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of all those who offered help.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks