Search code examples
google-sheetsgoogle-sheets-formula

How to sum / aggregate the results of "Filter" function

Very similar to this question, but for Google Sheets (I couldn't get any of the answers provided to work in Google Sheets).

I have a large list of timesheet data I'm trying to analyze. Here's what it looks like:

enter image description here

I'm trying to build a view of the data that allows me to select a date range and return rows showing how much time each staff person has spent on a given project with that date range, i.e.:

enter image description here

I've got the top row (project list) working using the below formula:

=transpose(sort(unique(filter({ARCHIVE!E:E}, ARCHIVE!B:B >= B28, ARCHIVE!C:C <= B29)),1,TRUE))

(Data is on a tab titled "ARCHIVE").

What I'm trying to figure out is how to fill in the rows below for each staff person.

By adjusting the filter criteria I can easily generate an array with project names and durations for each staff person, i.e.:

enter image description here

What I can't figure out how to do is take this array, sum the durations for each project, and generate a shorter array of the sums, i.e.:

enter image description here

Any help would be greatly appreciated.

Solution

  • Assuming your array of project names + durations are in columns A & B respectively, you can try this:

    =let(
projects,unique(A:A),
map(projects,lambda(k,if(k="","",sum(choosecols(filter(A:B,A:A=k),2))))))