Summarize and total data from another sheet

[lichldo]

Example sheet named "Project Estimate"

Stage 1
- Project Manager	5
- Account Director	10
- Account Director	30
- Copywriter	10
- Project Manager	5
- Project Manager	20
- Account Director	10
- Copywriter	5
- Account Director	10

Please see above table to demonstrate a sheet in my workbook named "Project Estimate"

Now I am trying to create a summary sheet.

In column A of my sheet named "Summary", I need to search a set of data from the sheet named Project Estimate that lists position titles, and input 1 of each unique value. So for example, using the above example I have "Project Manager" listed across 3 cells, "Account Director" listed across 4 cells, and "Copywriter" listed across 2 cells.
In my sheet named "Summary", I'd like A1 to = - Project Manager, A2 = - Account Director, A3 = - Copywriter. It doesn't matter the order, or the quantity, I just need a summary of all the roles listed.

I need it to only include a summary of cells that start with "-" and exclude all other cells

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", using the data in the example above, B1 for Project Manager = 30, B2 for Account Director = 60, B3 for Copywriter = 15

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

Thanks so much

 

[Austin Lang]

Hello,

I recreated the issue that you are having and this is working for me. On the summary page, this is the formula that it is using to count the data.

=SUMIFS('Project Estimate'!$B$2:$B$10,'Project Estimate'!$A$2:$A$10,"- Project Manager")

Note: On my summary page, I do not have the "- " in my table at the beginning, but it is looking for that on the Project Estimate sheet.

Hope this helps.

 

[lichldo]

Hello,

I recreated the issue that you are having and this is working for me. On the summary page, this is the formula that it is using to count the data.

=SUMIFS('Project Estimate'!$B$2:$B$10,'Project Estimate'!$A$2:$A$10,"- Project Manager")

View attachment 66650

Note: On my summary page, I do not have the "- " in my table at the beginning, but it is looking for that on the Project Estimate sheet.

Hope this helps.

Hi there, thank you. I realize I should have been a little more specific in my original post.

My actual set of data will be on the Project Estimate sheet across range the range $A$1:$AC$60 and there will be up to 20 values to summarize, where as in my example I only gave 3. I'm hoping there will be a formula that will read what is across my range, and then create a chart summarizing both the names, and the total. So I won't have a pre-set chart that just needs the numbers added in the column next to it.

And across my range, I have more data than just my 20 values and numbers, which is why I only want it to read the data that starts with " - "