Summarize and total data from another sheet

Please see attached to demonstrate what I am working with.

Using the Project Estimate sheet, I am trying to create a summary sheet.

In column A of my sheet named "Summary", I need to search the set of data from the sheet named Project Estimate that lists position titles, and input 1 of each unique value. This will be a different set of data/role every time, so the Summary sheet can not be pre-populated with roles. It needs to read everything on the Project Estimate sheet, identify all of the unique roles, and then put 1 of each in a cell on the Summary sheet. The important thing to note is that it must only list the roles that start with " - ". So for example in column A, it needs to exclude Video Phase, Copy Editing, Creative Design, Design Development, Artwork and Project Management, and only will from the other cells that start with " - ". These will be in different rows every time as you can see from the other columns, so it needs to be based on the text/value, not the cell name/range.

Then in column B of my sheet named "Summary, I need to total the numbers in Column B of the "Project Estimate" sheet next to each role.
So in my sheet named "Summary", I have included 1 example. I would want this formula to be able to identify that Project Manager Senior has 30 hours across all the data in Project Estimate

What would I need to input in my Summary sheet to get this to work?

Thanks so much

Hi, I appreciate this and it looks great but it will not meet my needs.

1 - I cannot reorganize the data as it is coming directly from another software

2 - I need the summary of hours in total for every phase - not per phase

Hi, thank you again, this does not accomplish what I am trying to do. The phases are irrelevant and not needed on the summary page. I need to combine each unique role from every phase and total the hours. So in your example sheet on the Output tab for example, "Creative Senior-Art Direction" is in row 3, 6, 12 with the hours from each phase. What I need is 1 row for that role with the sum of all hours across the phases.

I am also hoping to accomplish with just a formula as this will eventually be used with the online version of Excel which does not support VBA I think

Originally Posted by dosydos

See if the below workbook works for you. I created a new tab called "Formulas". In this tab in column A i have created 4 FILTER() functions as shown below. The red text below denotes i changed the column reference:

Formula:

Please Login or Register  to view this content.

The formulas are located in A2, A70, A156, A314. I left large gaps in between in case your data on the project Summary grows exponentially so that there will be no SPILL# errors.

Then on your Project Summary in A2, i created a Unique filter column to narrow down, remove duplicate job names and sort them.

Formula:

Please Login or Register  to view this content.

Finally in B2 i created a sumif formula to grab the total number of hours for each of the unique titles.

Formula:

Please Login or Register  to view this content.

You can hide the formulas tab if you want as well. But due to how your data is presented in the Estimate tab, a helper tab is a necessity.

Hope this helps.

I think this will work great thank you so much