Finding top sales people by month

[Matt_]

I'm looking for a formula that will show me my top monthly salespeople. I'm well aware that I can use a pivot table for this; I'm trying to learn how to do it via formulas.

I have 3 columns

Salesman | Amount | Date Signed

Note that each salesman will have multiple entries.


I know I can sumproduct() to find the total sales for each salesman and then large() off that, but is there a way to do it in one formula?

[MarvinP]

Hi Matt,

I really like Pivot Tables and they work great in Excel 2010. I think this should be an ovbious answer for you and a tool you should embrace.

You can do successive addition and never have to multiply but why would you want to, if multiplication is available?

Would a User Defined Function using VBA be a solution for you if it did the same thing as a Pivot Table? What other tools (that exist in Excel 2010) can't we use to solve your problem?

[Matt_]

Hi Matt,

I really like Pivot Tables and they work great in Excel 2010. I think this should be an ovbious answer for you and a tool you should embrace.

You can do successive addition and never have to multiply but why would you want to, if multiplication is available?

Would a User Defined Function using VBA be a solution for you if it did the same thing as a Pivot Table? What other tools (that exist in Excel 2010) can't we use to solve your problem?

The only way I want to know how to do it is if I can do it via one formula (I don't care how long/complicated it is). I know how to do it the other ways. I'm just (mostly) trying to learn more about formulas.

[oeldere]

Yes, combine the two formula's.

But I would (also) solve it with an pivot table.

[MarvinP]

Here Matt...

Learn Array Formulas as they will be the key to what you want.
http://www.cpearson.com/excel/ArrayFormulas.aspx
http://www.myonlinetraininghub.com/excel-array-formula
http://office.microsoft.com/en-us/ex...001087290.aspx