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

Question

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

What is the range of AllData? Does it include the header row? — Grisha Levit
When I put this all in the same sheet both formulas work. It appears there is an issue with the reference to the other sheet/tab. — Karl_S
@GrishaLevit The range of AllData is A:G. I reduced the range for the sample because I'm only using the three columns at the moment. AllData includes a header row, row 1. — Minadorae
@Karl_S Interesting. The sheet "AllData" is populated by a QUERY function that is pulling together data from other sheets (to become the single source of truth, as it were), could it be an issue with trying to reference the results of a query? — Minadorae
@Minadorae Make sure the spelling and case are the same. Copy the name to teh clipboard and paste it into your formulas. Outside of pulling that, the only other change Imade was the range on the Query as I ended up with the formulas on E1 and G1. The Query() gave me a header of "sum sessions" in G1 and a total in G2. — Karl_S

JPV JPV · Accepted Answer · 2017-01-18T13:25:37

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")

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

1 Answers