Search code examples
google-analyticsgoogle-sheetsgoogle-visualizationgoogle-query-language

Google Sheets FILTER() and QUERY() not working with SUM()

I'm trying to pull and sum data from one sheet on another. This is GA data being built into a report, so I have sessions split up by landing page and device type, and would like to group them in different ways.

I usually use FILTER() for this sort of thing, but it keeps returning a 0 sum. Thinking this may be an odd edge case with FILTER(), I switched to using QUERY() instead. That gave me an error, but a Google search doesn't offer much documentation about what the error actually means. Taking a guess that it could be indicating an issue with the data type (i.e. not numeric), I changed the format of the source from "Automatic" to "Number", but to no avail.

Maybe it's a lack of coffee, I'm at a loss as to why neither function is working to do a simple lookup and sum by criteria.

FILTER() function

SUM(FILTER(AllData!C:C,AllData!A:A="/chestnut/",AllData!B:B="desktop"))

No error, but returns 0 regardless of filter parameters.

QUERY() function

QUERY(AllData!A:G, "SELECT SUM(C) WHERE A='/chestnut/' AND B='desktop'",1)

Error returned:

Unable to parse query string for Function QUERY parameter 2: AVG_SUM_ONLY_NUMERIC

Sample data:

 landingPage | deviceCategory | sessions
-------------|----------------|----------
 /chestnut/  | desktop        |        4
 /chestnut/  | desktop        |        2
 /chestnut/  | tablet         |        5
 /chestnut/  | tablet         |        1
 /maple/     | desktop        |        1
 /maple/     | desktop        |        2
 /maple/     | mobile         |        3
 /maple/     | mobile         |        1
Solution

  • I think the summing doesn't work because your numbers are text formatted.

    See if any of these work? (change ranges to suit)

    using FILTER()

    =SUM(FILTER(VALUE(AllData!C:C),AllData!A:A="/chestnut/",AllData!B:B="desktop"))

    using QUERY()

    =ArrayFormula(QUERY({AllData!A:B, VALUE(AllData!C:C)}, "SELECT SUM(Col3) WHERE Col1='/chestnut/' AND Col2='desktop' label SUM(Col3)''",1))

    using SUMPRODUCT()

    =SUMPRODUCT(VALUE(AllData!C2:C),AllData!A2:A="/chestnut/",AllData!B2:B="desktop")