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.