Search code examples
google-sheetsgoogle-sheets-query

Google sheet -> filtering a query

I created the following sheet:

https://docs.google.com/spreadsheets/d/1AJmzlBlrDZ0mfb_9aSm83fzZxrwYWOxWGxjNhlfqPLA/edit?usp=sharing

I am trying to find a formula to calculate the totals (C7, C8, C9) and wherever it is copied ie C17, C18, C19)

So for C7 I want to sum column D (Duration) wherever:

  • the Date (col A) is the same as the total row date (that is A7)
  • The Category (col E) is Internal

Similarly for So for C8 I want to sum column D (Duration) wherever:

  • the Date (col A) is the same as the total row date (that is A8)
  • The Category (col E) is External

and

for C9 I want to sum column D (Duration) wherever:

  • the Date (col A) is the same as the total row date (that is A9)
  • Has any category

I tried for C7 -

=QUERY({A:E, arrayformula(N(D:D))}, "select sum(Col6) where Col5='Internal' and Col1 = " & A7 & " label sum(Col6) ''",1)

but I get NA ??? any help ? -

Solution

  • I wouldn't complicate life with a QUERY() here. Instead, just use FILTER().

    Total Internal: =SUM(FILTER(D:D, A:A=A7, E:E="Internal"))

    Total External: =SUM(FILTER(D:D, A:A=A8, E:E="External"))

    Total All: =SUM(FILTER(D:D, A:A=A9))