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.