0

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)),"")
Community
  • 1
  • 1
user2748807
  • 89
  • 3
  • 12
  • I'm not quite sure why your workaround includes a reference to column A, while the rest of your question doesn't - are you able to elaborate here? Also, can you confirm the "Time Spent" column is formatted as times/durations? – AdamL Mar 11 '15 at 21:54
  • I had to further filter my data based upon column A because there were duplicate project names. I have fixed that problem with my data and revised the workaround. – user2748807 Mar 12 '15 at 15:51
  • Time is in durations so 00:00:00 and it's possible that the values could be more than 24hrs. – user2748807 Mar 12 '15 at 16:16

2 Answers2

1

=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.

AdamL
  • 23,691
  • 6
  • 68
  • 59
  • This is really close only a couple of issues. It's possible that I will have more than 24 hrs but I don't see why I couldn't have hours listed greater than 24hrs? Or is the day(Col10) necessary? Also, I need to run this formula on one other spreadsheet in the adjacent column and for some reason I'm only able to do it once and the second formula for the second spreadsheet is just blank. No errors. – user2748807 Mar 12 '15 at 17:17
0

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.

pnuts
  • 58,317
  • 11
  • 87
  • 139
  • I don't think this would work well in this scenario. Sum and Query seem like the solution I just don't know how to apply it. See my workaround at the end for what I am trying to accomplish. – user2748807 Mar 11 '15 at 17:37