1

This question already has an answer here:

I want to get the sum of hours an employee, whose name is in Cell D1, was absent by referring to a table. The table is on a sheet named Sep. The word Sep is a string typed in A97. I am able to accomplish the query within my workbook, with the formula:

=QUERY(indirect(concatenate($A97,"!A2:$F")), "Select sum(F) where A = '"&$D$1&"' label sum(F)'' ")

I also verified that my IMPORTRANGE formula is working. (Because my Stack Exchange account is new, it won't allow me to put more than two links in this posting. So, I took out the full link in the formula below, even though I correctly use the full link in my actual formula.):

 =importrange("1033hNIUutMjjdwiZZ40u59Q8DvxBXYr7pcWyRRHAdXk",concatenate($A97,"!A2:$F"))

But, when I try and put it all together, I get the error:

Error Unable to parse query string for Function QUERY parameter 2: NO_COLUMN: F

This is the formula I'm attempting to use:

=Query(importrange("https://docs.google.com/spreadsheets/d/1033hNIUutMjjdwiZZ40u59Q8DvxBXYr7pcWyRRHAdXk",concatenate($A97,"!F2:$F300")), "Select sum(F) where A = '"&$D$1&"' label sum(F)'' ")

Here is a link to the Google Spreadsheets.

marked as duplicate by Rubén google-sheets Oct 2 at 4:56

This question has been asked before and already has an answer. If those answers do not fully address your question, please ask a new question.

  • I added a sheet called "SO -Aurielle" Let me know if that is the result your looking for and ill add in the details for the answer – Aurielle Perlmann Oct 2 '16 at 4:30
  • This is very helpful... actually for some other projects I have going. But, I was hoping to just get the number by itself (not in a table). I have a payroll spreadsheet, and I just want a single cell for this employee to have 8.5. – Neill Dorn Oct 4 '16 at 23:12
  • I updated my answer - if you remove the Col1 and wrap in index you will get only that cell: To only get the cell by itself you have to remove the "select" Col1 from the first formula and also wrap it in index to only get the row you care about: =index(Query(importrange("https://docs.google.com/spreadsheets/d/1033hNIUutMjjdwiZZ40u59Q8DvxBXYr7pcWyRRHAdXk",$A97&"!A2:$F"), "Select sum(Col6) where (Col1='"&$D$1&"')"),2,) – Aurielle Perlmann Oct 4 '16 at 23:19
  • Thank you SO much! It makes sense to me now, kind of... What matters most is that it's now working in my spreadsheet! Thanks again! – Neill Dorn Oct 7 '16 at 4:39
1

I added a sheet on your doc with 3 variations on how you can get the data you want...

enter image description here

To only get the cell by itself you have to remove the "select" Col1 from the first formula and also wrap it in index to only get the row you care about:

=index(Query(importrange("https://docs.google.com/spreadsheets/d/1033hNIUutMjjdwiZZ40u59Q8DvxBXYr7pcWyRRHAdXk",$A97&"!A2:$F"), "Select sum(Col6) where (Col1='"&$D$1&"')"),2,)

To be super specific like you asked in your question by point to column D you can do this:

=Query(importrange("https://docs.google.com/spreadsheets/d/1033hNIUutMjjdwiZZ40u59Q8DvxBXYr7pcWyRRHAdXk",$A97&"!A2:$F"), "Select Col1,sum(Col6) where (Col1='"&$D$1&"') group by Col1")

enter image description here

If you actually wanted a small table that aggregates all the data by unique name with summed values you can just use pivot or group by within query:

For group by:

=Query(importrange("https://docs.google.com/spreadsheets/d/1033hNIUutMjjdwiZZ40u59Q8DvxBXYr7pcWyRRHAdXk",$A97&"!A2:$F"), "Select Col1,sum(Col6) where Col1<>''group by Col1",)

enter image description here

For pivot (my favorite):

=Query(importrange("https://docs.google.com/spreadsheets/d/1033hNIUutMjjdwiZZ40u59Q8DvxBXYr7pcWyRRHAdXk",$A97&"!A2:$F"), "Select sum(Col6) where Col1<>'' PIVOT Col1")

I like to add headers on top and transpose the query horizontally so it looks like this:

={"Employee","Hours";TRANSPOSE(Query(importrange("https://docs.google.com/spreadsheets/d/1033hNIUutMjjdwiZZ40u59Q8DvxBXYr7pcWyRRHAdXk",$A97&"!A2:$F"), "Select sum(Col6) where Col1<>'' PIVOT Col1"))}

enter image description here

Not the answer you're looking for? Browse other questions tagged or ask your own question.