after hours of try and error I decided to ask the community.
At the moment I create a sheduling doc, which contains Dashboards, planning and reporting areas for different teams within the same department. The reporting shall be even on individual level at the end.
Basically I have a core table, which contains all shifts ever planned and for reporting I want to know, who was planned how long for each tasks in defined time frames (like: person A did 60 hours phone calls and 40 hours e-mails with the last 3 weeks).
I want to have the individual results aggregated within 1 single, extra table.
My basic table looks like this:
Name +++ Date +++ Tasks +++ 1 Tasks 2 +++ Task 3
(Person A +++ today +++ Phone +++ Mail +++ Chat)
(Person B +++ today +++ Phone +++ Phone +++ Phone)
(Person A +++ yesterday +++ Mail +++ Mail +++ Mail)
The extra table shall look like this:
Name +++ Phone (hrs assigned) +++ Mail (hrs assigned) etc.
(Person A +++ 60 hrs +++ 40 hrs
(Person B +++ 40 hrs +++ 0 hrs)
I tried “lookup”, “filter”, “contains”, sum, sumif etc… But I can’t find a way. I could create an extra view, filtering for a specific person, but that’s too much work for 100+ people.
Is there any way to “filter”/aggregate the data within a formula?