1

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?

2

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
1

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)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.