Thursday, October 18, 2012

Google Spreadsheet Query Group by

( by Johnny Wang88 )

Hi, Google docs Pros!!

I only know how to use sum in google docs, but would like to know how to use filter or search to sum the fields that I want. Here is what I need. I have a bunch of data like below:

as could be seen above, there will be different names and numbers for each name, and this datasheet will be growing each months with new data with more names and numbers, I want to sum the numbers of each specific names, for example like the result:

Thanks so much in this is probably very easy for Pros, but I've tried 2-3 hours still can't figure it out!! need help! Thanks!!


Suppose if you have the data in 'Sheet1' and then if you want to have the results in 'Sheet2' then look at the following example:

Have a look at the following screenshot of 'Sheet1':

If you want the results in same sheet then use the following formula:
=query(A:B;"select A,sum(B) where A <> '' group by A label sum(B) '' ")
OR you can also use the following formula:
=query(ArrayFormula(A:B);"select Col1,sum(Col2) where Col1 <> '' group by Col1 label sum(Col2) '' ")

And if you want the results in new Sheet then, make a new sheet, lets call it 'Sheet2'

In 'Sheet2' put the following formula in Cell A1:
=query(Sheet1!A:B;"select A,sum(B) where A <> '' group by A label sum(B) '' ")

Alternatively you can also use the following formula:
=query(ArrayFormula(Sheet1!A:B);"select Col1,sum(Col2) where Col1 <> '' group by Col1 label sum(Col2) '' ")

The above query will fill the data as shown in the screenshot below:
Have a look at the following screenshot of 'Sheet2':

I hope the above solution will help you, and if you need more help then please do comment below on this blog itself, I will try to help you out.

I also take up private or confidential projects:

If this blog post was helpful to you, and if you think you want to help me too and make my this blog survive then please donate here: 



  1. thanks for your quick reply, I think this is exactly what I want. Thank a millions!! Just want to ask what does where A <> means? I know capital A is for column A, what does <> mean in the code there?

  2. Most Welcome.....
    Here the "A<>" means where A is not equal null ( or blank )

    equal to means =
    not equal to means <>

    I hope you understand it now.

  3. you are so great! I understood now. Thanks a lot!! I have another question, if I have a specific column that I want to include in the query from other source of sheet, how could I do that? For example:

    The age of the name,

    Bill is 56
    Chris is 32
    Dick is 23
    John is 44
    Mary is 31

    How could I show them after the added number column? Thanks.

    1. If you can provide me the sample spreadsheet then may be I can help you!! :)

  4. thanks, here is the sample spreadsheet,

    I want to use query from sheet 1 and sheet 2 to get result like shown in sheet 3

    thanks a millions

  5. That's very interesting!!
    Is there any way to do the same with Strings?
    I mean, in your example they are numbers, and you do SUM... but if they weren't numbers but strings?

    something like "select A, join(", ",B) where A <> "group by A" "
    And the other hand, why "group by A" is between " ???

    Thanks so much