**Question:**

( by Johnny Wang88 )

Hi, Google docs Pros!!

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:

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

Thanks,

Kishan,

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?

ReplyDeleteMost Welcome.....

ReplyDeleteHere the "A<>" means where A is not equal null ( or blank )

equal to means =

not equal to means <>

I hope you understand it now.

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:

ReplyDeleteThe 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.

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

Deletethanks, here is the sample spreadsheet,

ReplyDeletehttps://docs.google.com/spreadsheet/ccc?key=0AlQ8FWPdXEoTdGx5a0RlSm1jZ3ZDZ29Ram94OGg4eHc

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

thanks a millions

Really thank you!!!

ReplyDeleteThank you so much!

ReplyDeleteThat's very interesting!!

ReplyDeleteIs 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