Forum Discussion

Liz McNeil's avatar
Liz McNeil
Copper Contributor
Nov 15, 2017

Calculate Resource Totals Across Time Period

Not an Excel power user so I could use some help : )

 

 

I have a spreadsheet that shows resource allocation by project, but I want to have a view where I can see totals for each week for each resource. Screenshot below. The overall project load is much larger than shown here

 

 

 

 

 

For example, I would want to see total hours by week for resource Kristina so I can determine overages for each resource. Is this best achieved via a pivot table or particular formula? 

 

Any help is appreciated!!

Charting
excel
Formulas and Functions
office 365

1 Reply

Sort By
  • SergeiBaklan's avatar
    SergeiBaklan
    MVP
    Nov 16, 2017

    Hi Liz,

     

    Both works. With formulas you shall create another table with unique names there you will collect summaries. With PivotTable unique names will be generated automatically, all weeks are to be added to calculations of summary.

     

    Perhaps the easiest way is to use Power Query (aka Get&Transform), that is few steps script which returns you the summary table, for that sample

    the script generated from user interface is

    let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(Source, {"Project", "Name"}, "Attribute", "Value"),
    #"Grouped Rows" = Table.Group(#"Unpivoted Columns", {"Name", "Attribute"}, {{"Totals", each List.Sum([Value]), type number}}),
    #"Pivoted Column" = Table.Pivot(Table.TransformColumnTypes(#"Grouped Rows", {{"Attribute", type text}}, "en-GB"), List.Distinct(Table.TransformColumnTypes(#"Grouped Rows", {{"Attribute", type text}}, "en-GB")[Attribute]), "Attribute", "Totals", List.Sum)
in
    #"Pivoted Column"

    and in attached file.

Resources