Summing based on words

[Snappleton]

I will do my best to describe my problem. Our company has a program that summarizes weekly and job wide labor hours. It can be exported into Excel which is what my tab labeled "Data" is. My "Analysis" tab is where my question comes in. My columns Q and R are the only places that will have an impact on columns S-V. I have figured out how to do what I want line by line but there are rows that contain "Floor" totals as well as "Building" totals. What I would like is that the "Floor" rows would summarize what was done on the rows above but stopping before the next row where there is "Floor". Also do the same for "Building" which would summarize "Floor" above and stopping if it sees another "Building". If there are any questions as to what I am asking, please post. Attached is my sample file. Number of rows will change based on the project but would rarely, if ever, exceed 500 rows. There is additional data on the "Data" tab at the bottom but I am ignoring that for now.

Thank you in advance for any assistance. Forgive my less than elegant Excel within my sample.

 

[vletm]

How do You export data into Excel?
... now, Your data-sheet and ... analysis-sheet has challenges:
... eg data-sheet's cells F2 's data seems to be one column left ... why? is that Your 'motive' to have analysis-sheet?
= could the 'export' modify ... that headers and data would match?
>>> is there really need to have analysis-sheet? ... by formulas?

Your file should show (even manually solved) - what would You need to get there?
... or could other kind of analysis-sheet be possible?
Which shows only ... sums or what ever You would need to have?
Note: below headers and so are ... rough sample.

 

[Snappleton]

The shift in cells is to align format for printing. The export takes the raw data (Crystal (RPT)) and when exported into Excel does not align columns and headers.
The formulas are to allow the team to state what they believe are the required hours to complete a phase code (row) or percent complete for the same line.
I didn't want to use the filter option as I am trying to make it as simple for the people doing entry as possible. Many are not well versed in Excel and i would only anticipate the sheet getting broken if I had too many options.
I hope those help you to help me. Let me know if any of the responses don't make sense.

 

[vletm]

You analysis-sheet is full of same formulas. My opinion is that's something else than ... safe.
... eg someone write in cell A18 1118 ... after that there will be always value 1118.
>> even Data-sheet would be more safe for all users ... it could always reload.
... even those headers could move in correct places.

Did You notice my questions:
Your file should show (even manually solved) - what would You need to get there?
... or could other kind of analysis-sheet be possible?
Which shows only ... sums or what ever You would need to have?
Where could I see that?

You've originally asked about summing based on words
... above You wrote something based rows.

 

[Snappleton]

I intend to protect the analysis tab and only allow entry by the users in Q, R and W. Each user will export his/her project from our internal system to XLSX and then drop that into the data tab.

The export does zero calculations and will not allow manipulation of anything. That is the reason for this separate spreadsheet. Each row in Q or R would be entered by the manager to determine what they think versus what the project currently shows.

The summing I want to do, as an example, would be to sum rows S44:S53 in S54 (Floor) repeat same task for the subsequent floors and then a further summary at building level in S31.

 

[vletm]

Do Your sample file match with Your real file?
... I've an image that there are differences.

# I intend to ... is it safe procedure?
# The export does ... so?
# Your eg cell S54 has =IFERROR(IF(X54=TRUE,R54,(J54/Q54)-J54),"") ... and You wrote about summing something?
I've a challenge to figure - what do You need? how to see - what do You need? as well as full of same formulas

 

[Snappleton]

Do Your sample file match with Your real file? Yes, I only changed some naming on certain lines as the information is proprietary. All data is as it is exported.
... I've an image that there are differences.

# I intend to ... is it safe procedure? Not sure what you are asking.
# The export does ... so? Because the export does not do calculations we cannot use it to attain the desired results.
# Your eg cell S54 has =IFERROR(IF(X54=TRUE,R54,(J54/Q54)-J54),"") ... and You wrote about summing something?
I've a challenge to figure - what do You need? how to see - what do You need? as well as full of same formulas I think I need a new formula in every S cell to get the results I am looking for. That is the question that I have. I don't care if every cell has to be changed so long as the results are the same. I am not an expert and am looking for a way to attain the results that I desire.

 

[vletm]

# ... I only changed some ... for me, match is ... match.
# Is Your way safe procedure ... eg drop that into the data tab
... of course, You can image that ... safe procedure ... to drop
# Check this file ... the manager could do something with Q and R.
... and there seems to be fixed rows data per floor >> You could get those floors sums.
## Still I have missed ... those Your expected results with some values ##

 

[Snappleton]

I did not describe the problem properly. This is just an example from one project. Every job will have a different number of rows and the location of where "floor" and "building" are will vary substantially. That is the reason I need it to sum automatically by floor and again by building and my idea was to read those words to do a calculation which is different from the rows that are not floor or building.
The format of the export cannot be modified unless that is done manually which is another part of what I am trying to eliminate. Export, drop into data tab, then the manager can enter their values on the analysis tab.

 

[vletm]

I could copy & paste my previous writings ...
... but You could write something other things again.