Google Spreadsheet Query Group By

Question:

( by Mikkel Holm )

Hi,

I'm organizing the amount of sales for my company in a spreadsheet, and I would like to see how much provenue every agent generates.

I know to count the amount of orders pr agent, by using the countif formula, but I'm having a hard time figuring out how to sum the value of orders made by a specific agent.

In other words I'm searching for a formula to sum the values of column B if column A is "XX" in column F. For example in F2 should be the summed value of all the cells i column B, where the initials "MKH" is in the adjacent A cell.

	A	B	C	D	E	F
1	Name	Value	Occurrences pr name		Value pr name
2	MKH	100	MKH	4	MKH	?
3	LT	200	LT	3	LT	?
4	LT	200
5	LT	200
6	MKH	100
7	MKH	100
8	MKH	500

Does anyone have a good idea? Your help will be much appreciated.

Cheers

Mike.

Solution:

Have a look at the following screenshot:

In the above sheet I have the following formula in Cell D1:

=query(A:B;"select A,count(B),sum(B) where A<>'' group by A")

and if you want to label the column header then try the following formula:

=query(A:B;"select A,count(B),sum(B) where A<>'' group by A label count(B) 'Count',sum(B) 'Sum'")

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 and confidential projects:

http://igoogledrive.blogspot.com/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