Google-sheets – Google Sheets Query sum single column based on criteria

google sheetsgoogle-sheets-query

I have a spreadsheet laid out with three columns (a,b,c) as such:

a       b       c    
--      --      --
abc     12      yes
abc     8       yes
xyz     10      no
xyz     5       no
lmn     8       yes

What I am trying to do is write a query to output with three columns: a, sum(b). Then I want to add in a 3rd column that is sum(c) where c='yes' and only this column has this filter. All Should be grouped on column a. So my result should look like this:

a       b       c    
--      --      --
abc     20      20
xyz     15      0
lmn     8       8

Getting columns a and b as I want them is easy but I don't know how to get column C given just that column has the criteria to sum only if column C = 'yes'. How do I add the last column to this query to produce column C results:

=QUERY('Raw Data'!A2:C, "Select A, Sum(B) group by A")

Any ideas?

Best Answer

I think, you need the combination of formulas. The answer is:

={QUERY({A:C},"select Col1, sum(Col2) where Col1 <>'' group by Col1"),{"filtered sum";ArrayFormula(IFERROR(VLOOKUP(UNIQUE(FILTER(A2:A,A2:A<>"")),QUERY({A:C},"select Col1, sum(Col2) where Col3 ='yes' group by Col1") ,2,0),0))}}

Explanation

It's not hard if you'll take it by parts:

  • ={basic query, {"header"; vlookup(a, help query, 2, 0) }}

Basic query

QUERY({A:C},"select Col1, sum(Col2) where Col1 <>'' group by Col1")

It's simple, I've used Col1, Col2... notation to make it work with any range.

Vlookup

IFERROR(VLOOKUP(UNIQUE(FILTER(A2:A,A2:A<>"")), help query ,2,0),0))

We count sums with criteria (c = 'yes') in the help query.

UNIQUE(FILTER(A2:A,A2:A<>"")) part of the formula gives you a list from column 'a'.

Help query

QUERY({A:C},"select Col1, sum(Col2) where Col3 ='yes' group by Col1")

Here you may enter any conditions what you want. In this case it's Col3 ='yes'

Related Topic