OzGrid

How to determine the top sales person

< Back to Search results

 Category: [Excel]  Demo Available 

How to determine the top sales person

 

Requirement:

 

The user requires a formula to find the top sales person by sales and units as per below:

 

 

Solution:

 

 

How many units did Bob Sell?:  =SUM(SUMIF(D7:D227,{"Bob"},B7:B227))

 

How many units did Bob Sell on 6/5/2014:  =INDEX(B7:B78,MATCH(1,(A7:A78=DATE(2014,6,5))*(D7:D78="Bob"),0))

 

Which sales person sold the most Units?:  

=IF(MAX(SUMIF(D:D,"Joe",B:B),SUMIF(D:D,"John",B:B),SUMIF(D:D,"Bob",B:B),SUMIF(D:D,"Steve",B:B))=SUMIF(D:D,"Joe",B:B),"Joe",IF(MAX(SUMIF(D:D,"Joe",B:B),SUMIF(D:D,"John",B:B),SUMIF(D:D,"Bob",B:B),SUMIF(D:D,"Steve",B:B))=SUMIF(D:D,"John",B:B),"John",IF(MAX(SUMIF(D:D,"Joe",B:B),SUMIF(D:D,"John",B:B),SUMIF(D:D,"Bob",B:B),SUMIF(D:D,"Steve",B:B))=SUMIF(D:D,"Bob",B:B),"Bob","Steve")))

 

Which sales person had the most revenue?

=IF(MAX(SUMIF(D:D,"Joe",C:C),SUMIF(D:D,"John",C:C),SUMIF(D:D,"Bob",C:C),SUMIF(D:D,"Steve",C:C))=SUMIF(D:D,"Joe",C:C),"Joe",IF(MAX(SUMIF(D:D,"Joe",C:C),SUMIF(D:D,"John",C:C),SUMIF(D:D,"Bob",C:C),SUMIF(D:D,"Steve",C:C))=SUMIF(D:D,"John",C:C),"John",IF(MAX(SUMIF(D:D,"Joe",C:C),SUMIF(D:D,"John",C:C),SUMIF(D:D,"Bob",C:C),SUMIF(D:D,"Steve",C:C))=SUMIF(D:D,"Bob",C:C),"Bob","Steve")))

 

Obtained from the OzGrid Help Forum.

Solution provided by chirayuw.

 

See also: Index to Excel VBA Code and Index to Excel Freebies and Lesson 1 - Excel Fundamentals and Index to how to… providing a range of solutions and Index to new resources and reference sheets

 

See also:

How to look and sort blocks of rows
How to use VBA code to sort worksheets based on a pre-sorted named-range
How to sort results after copying data from multiple sheets

 

Click here to visit our Free 24/7 Excel/VBA Help Forum where there are thousands of posts you can get information from, or you can join the Forum and post your own questions.

 

 

 

 


Gallery



stars (0 Reviews)