5

My sheet is here, and we are looking on the sheet called "Paste".

I would like a query that finds and sums the total dollar amounts when the same customer receives the same service code more than once, on the same date. As you can see on sheet UsuallyAnotherSpreadsheetFile, the customer Brown, Misses /e received a service coded 0123G 0124G more than once on 2016-10-12. There are other dates where the overlap occurs, and I've colored them for easy visual reference.

As an example, taking the original data of:

Brown, Misses /e    Bumbler, Brandi 0123G 0124G 2016-10-12   9:00 AM    10:15 AM    118.44  148.05  1.25
Brown, Misses /e    Bumbler, Brandi 0123G 0124G 2016-10-12  12:30 PM     1:15 PM    118.44   88.83  0.75
Brown, Misses /e    Bumbler, Brandi 0123G 0124G 2016-10-12   2:30 PM     3:00 PM    118.44   59.22  0.50

I want to show result(date, billing code, total charge, total units):

2016-10-12 0123G 0124G  296.10  2.50

I wish I could say something like this (but it doesn't work) ... see cell B3:

=QUERY(ARRAYFORMULA(IMPORTRANGE(
"https://docs.google.com/spreadsheets/d/1Wyewpz0j_IiKK8ESOapX5L4AedjvHccLZOOKS2tZGUA/",
"UsuallyAnotherSpreadsheetFile!B2:K")),
"Select Col4, Col3, sum(Col8), sum(Col9) where (Col1 = '" & ClientInfo!$H$1 & "') 
***group by Col4 and Col3*** label sum(Col8) '', sum(Col9) ''")

So, it would appear that I'm not allowed to group by two conditions. I'm not really a programmer, so I am just reaching out for someone more experienced to help out.

1

1 Answer 1

3

Replace and by ,.

Final formula:

=QUERY(ARRAYFORMULA(IMPORTRANGE(
"https://docs.google.com/spreadsheets/d/1Wyewpz0j_IiKK8ESOapX5L4AedjvHccLZOOKS2tZGUA/",
"UsuallyAnotherSpreadsheetFile!B2:K")),"Select Col4, Col3, sum(Col8), sum(Col9) 
where (Col1 = '" & ClientInfo!$H$1 & "') group by Col4, Col3 label sum(Col8) '',
sum(Col9) ''")

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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