Summarize and total data from another sheet

lichldo

New Member
Joined
Apr 19, 2022
Messages
45
Office Version
  1. 365
Platform
  1. MacOS
Example sheet named "Project Estimate"
Stage 1
- Project Manager5
- Account Director10
- Account Director30
- Copywriter10
- Project Manager5
- Project Manager20
- Account Director10
- Copywriter5
- Account Director10

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
 
Last edited:

Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.

Austin Lang

New Member
Joined
Sep 10, 2021
Messages
25
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
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")

1654732475098.png


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

New Member
Joined
Apr 19, 2022
Messages
45
Office Version
  1. 365
Platform
  1. MacOS
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 " - "
 

Forum statistics

Threads
1,176,640
Messages
5,904,198
Members
435,076
Latest member
71672618

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top