help auto sum and total sum on expanding sheet & create historical table + master and working copy

Copper Contributor

 how do i calculate a sum of numbers thats 

i have a sheet that is a log of daily cleaning / work / how far we got / notes

i need to calculate the sum of each location (some locations have more than 1 area in the location so i will need a total of that location) so I will need a total for each area then by the whole location

then the next area i need to do the same thing with a sum of each area within the location then a total of the location(all areas totalled together)

 

and i need it to be able to expand as we will be adding more info to the table as we are working on the job over the years

im currently working on this years because we are doing the job now but I am creating a historical data sheet with all previous years on it and having a working(for the guys to add in data for the day while working) and master copy that their data syncs into and cant be altered. 

(i also will need to sort the data by location (each area) and by date in ascending order

i included my files im working from/on
1.) new workbook i made for it (to have the tables for the historical data)(AN-05-10-23 SMI worksheet
2.) sheet i copied from the SMI worksheet for the guys to use while i build the other sheet(2023 working field notes)
3.) and an old sheet from a previous year to be able to view what it will look  like filled in. (2021 cics notes) this is what i need to use to do the historical data (i still have to scan in all the paper sheets from 2022 and upload them into one sheet/table to get it into the historical)

3 Replies
I suggest you try to make this easy on yourself. Some suggestions:
- Try to get all your data into one single flattened table
- Make sure this table has separate columns for date, location and any other type of information you would want to get sub-total amounts grouped for
- Do not enter any formulas within that table, this is data, not your report.
With your data well structured, creating your report is as simple as inserting a pivot table using your new table of data as its source. No formulas needed whatsoever.
will it be a running table for the guys to insert the new info into or how do we keep it calculating daily?
the subtotals would be for each area of the location EX: SELF is one area then theres SELF AREA 2, SELF area 3, SELF bumpout

then we have WEX as location then WEX, WEX stage 3, stafe 4, stage 5, stage 6
so i would need subtotal for all of those then a grand total of WEX, and so on for each area

i would need to be able to do the sums for the duration of the job so we can send it to them weekly as its appx a 6 month job throughtout the year

then