# Sum only parts of a table

Hi,

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:
(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?

1 Like

Hi,

[Report Raw Data All Agents] = table was all single shifts being entered
[Reports: Core Times (allocated hours per task per agent)] = table, where each person is mentioned once only. Here I want to see the aggregated results for each individual.

Basically I wanted to try to have a match in the formular like:
If(thisRow-Name=[Report Raw Data All Agents].Name,SUM([Report Raw Data All Agents].[Count Phone]),0)

The result is always 0.

[Report Raw Data All Agents].[9:00].CountIf((CurrentValue=â€śPhoneâ€ť))

But also here I did not figure out how I filter the data for single persons.

Does anyone have an idea?

I think youâ€™re on the right track with filtering.

Hereâ€™s a simplistic example:

This just filters based on names, but you could expand this to handle the activity type too.

Right now the formula is `SUM(Activity.Filter(Name=SummaryName).Amount)`

If you imagine also adding a column to the Activity table called Type, with Select dropdowns for Phone vs Mail, and then adding a column to the Summary table called SummaryType, your formula would instead be:
`SUM(Activity.Filter(Name=SummaryName && Type=SummaryType).Amount)`

Note: Itâ€™s not necessary to have the â€śnameâ€ť column in the summary table be called something different (both tables can have columns called â€śnameâ€ť), but itâ€™s easier to understand the formula this way

1 Like

@Nick_HE

I tried to rebuild it:

For some reason it says the result is always 0. When I replace â€śName (Overview)â€ť by a specific name it works perfectly. Any idea, why?

Can it be a problem, that one â€śnameâ€ť column is formatted as â€ślookup from tableâ€ť?

Yep I think so - one column is just text, and the other is a living breathing connection to the record in the other table, so theyâ€™re not strictly speaking â€śequal.â€ť

You can either:

• make the columns in both tables be formatted as lookup from table, or
• for the one thatâ€™s a lookup, add a dot reference. so instead of saying `Filter(Name=` you would go `Filter(Name.blabla=` where â€śblablaâ€ť is a column reference within your people table that youâ€™re looking up in (so maybe something like `Name.[First Name]` or whatever you called the column in your people table.

Ah thanks god. Finally it works!