## Thursday, October 18, 2012

Question:

( by Johnny Wang88 )

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:

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,

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. Really thank you!!!

6. Thank you so much!

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