CODEWITHSUNDEEP
google-sheetsgoogle-sheets-formulaarray-formulasgoogle-sheets-queryimportrange
user2748807
user2748807

Reputation: 109

SUM, FILTER, QUERY and IMPORTRANGE and match to data

On a separate spreadsheet I have project names in column 2 (B2:B). In column 10 (J2:J) of that same spreadsheet I have time (duration) spent on that project. These projects are repeated and additional time spent on them so they show up multiple times in the list. Something like this:

Project Name    Time Spent
12-001 John     2:13:45
12-002 Sally    0:34:45
12-003 Tim      1:56:12
13-006 Bruce    3:34:12
12-001 John     3:45:09
12-003 Tim      0:38:56
12-001 John     1:00:00

In the parent spreadsheet where the formula will be located I have a master list of all projects located in column 2 (B2:B). I would like to sum and filter the data on the other spreadsheet and match it up with the correct project name. So something like this:

Project Name    Time Spent
12-001 John     6:58:54
12-002 Sally    0:34:45
12-003 Tim      2:35:08
13-006 Bruce    3:34:12

I have been trying:

=sum(filter(importrange("key","Timesheet!A2:J254"),importrange("key","Timesheet!B2")=B2,))

=sum(query(importrange("key","Timesheet!A2:J254"),"Select Col10 where Col2 ="&B2&")

which do not work.

I do have a workaround which is to just do a sum and filter of three IMPORTRANGE functions all in the same formula but this means I have to run the IMPORTRANGE thousands of times? Not very efficient:

=iferror(sum(filter(IMPORTRANGE("key","Timesheet!J2:J"),IMPORTRANGE("key","Timesheet!B2:B")=B2)),"")

Upvotes: 0

Views: 2923

Answers (2)

AdamL
AdamL

Reputation: 24609

=ArrayFormula(IFERROR(VLOOKUP(B2:B,QUERY(QUERY(ImportRange("key","Timesheet!A2:J254"),"select Col2, hour(Col10)/24+minute(Col10)/1440+second(Col10)/86400"),"select Col1, sum(Col2) group by Col1"),2,0)))

  1. QUERY is the ideal choice when using ImportRange, as it allows a lot of data manipulation/aggregation with one ImportRange call.

  2. QUERY can't aggregate time values natively, hence the convoluted conversion to numeric values with hour(Col10)/24+minute(Col10)/1440+second(Col10)/86400. If you had durations that were greater than a day, you would need to include day(Col10) as well.

  3. The nested QUERY functions then provide a lookup table, which VLOOKUP uses to perform an "array lookup" of the master list of project names. Again, this enables the use of one ImportRange call, rather than having to fill a formula down.

Upvotes: 1

pnuts
pnuts

Reputation: 59460

Might not suit OP but the conventional solution is probably a pivot table:

SO28970025 example

Project Name for Rows, Summarise by : SUM Time Spent for Values.

Upvotes: 0

Related Questions