## Saturday, September 21, 2013

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:
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,