Google Spreadsheet Query Group by

Question:
 
( 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 advance....as this is probably very easy for Pros, but I've tried 2-3 hours still can't figure it out!! need help! Thanks!!

Johnny

Solution:

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:

http://igoogledrive.blogspot.in/p/paid-projects.html

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: http://igoogledrive.blogspot.com/2012/09/donate.html 

Thanks,

Kishan,

iGoogleDrive