Summing hours into a grid by employee by day

[tdomer80]

I have a simple raw data sheet of employee hours charged to jobs during a week, and I am trying to create a visual (will use a macro) to show total hours by day by associate, rather than using a pivot table.

I am not sure whether this is a nested vlookup or an index/match thing to get this. I am probably over-thinking it...

Any ideas?

 

[sandy666]

maybe Power Query (Get&Transform)

Employee	Date	Job	Hours		Employee	20/07/2020	21/07/2020	22/07/2020
101-TERESA	20/07/2020	1	8		101-TERESA	8	8	8
101-TERESA	21/07/2020	1	8		104-CARSON	4.5	8	8
101-TERESA	22/07/2020	1	8		142 - NATHAN	10	8	8
104-CARSON	20/07/2020	1	4.5
104-CARSON	21/07/2020	1	8
104-CARSON	22/07/2020	1	8
142 - NATHAN	20/07/2020	1	2.5
142 - NATHAN	20/07/2020	2	2
142 - NATHAN	20/07/2020	3	5.5
142 - NATHAN	21/07/2020	1	8
142 - NATHAN	22/07/2020	1	6
142 - NATHAN	22/07/2020	2	2

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Type = Table.TransformColumnTypes(Source,{{"Employee", type text}, {"Date", type date}, {"Job", Int64.Type}, {"Hours", type number}}),
    RC = Table.RemoveColumns(Type,{"Job"}),
    Pivot = Table.Pivot(Table.TransformColumnTypes(RC, {{"Date", type text}}, "en-GB"), List.Distinct(Table.TransformColumnTypes(RC, {{"Date", type text}}, "en-GB")[Date]), "Date", "Hours", List.Sum)
in
    Pivot

 

[Multiverse]

Or a SUMIFS (where row 2 is the date)

Formula:

Result:

 

[tdomer80]

Thank you so much! I am just now dipping my toe into the water on sumifS !!

If I had been doing this for me I would have done a pivot table, but I think this will work nicely for others who need to view a spreadsheet without much intervention from me...

 

[sandy666]

note: try to write to whom you are addressing your post