# Google Sheets Query sum single column based on criteria

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?

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'`

• Thanks for the in depth response! I see where you are going but unfortunately this doesn't quite add up though. The filtered sum values for xyz and lmn are wrong. abc looks good though which is weird. – slybitz Sep 30 '16 at 18:02
• Down below, I just posted the answer I was able to finally come up with. – slybitz Sep 30 '16 at 18:10

I was able to come up with the answer:

``````=query({A2:B,ArrayFormula(if(C2:C="yes", B2:B,0))}, "select Col1, sum(Col2), sum(Col3) where Col1 <>'' group by Col1 label sum(Col2)'', sum(Col3)''", 0)
``````