PDA

View Full Version : Using Index, Match, Max & Sumifs to locate top sales person

vms777
2017-03-16, 05:48 AM
Hi there, I'm trying to use a formula to answer the following question: "Who has sold the most units" (not using a pivot table).

I believe it will be a combination of index match and max and sumif. In essence, I need it to sum the total units sold for each person, and return the name of the person with the most units sold. For the life of me I can't figure it out after trying so many things. Thank you so much for any assistance.

Date
Units Sold
Revenue
Sales Person

6/1/14
79
\$772.00
Joe

6/2/14
89
\$234.00
Joe

6/3/14
90
\$488.00
Joe

6/4/14
50
\$633.00
Joe

6/5/14
55
\$379.00
Joe

6/6/14
99
\$532.00
Joe

6/7/14
79
\$899.00
Joe

6/8/14
51
\$756.00
Joe

6/9/14
71
\$325.00
Joe

6/10/14
70
\$890.00
Joe

6/11/14
45
\$123.00
Joe

6/12/14
41
\$200.00
Joe

6/13/14
51
\$316.00
Joe

6/14/14
83
\$763.00
Joe

6/15/14
70
\$623.00
Joe

6/16/14
65
\$339.00
Joe

6/17/14
88
\$94.00
Joe

6/18/14
12
\$813.00
Joe

6/1/14
20
\$603.00
John

6/2/14
32
\$883.00
John

6/3/14
62
\$128.00
John

6/4/14
80
\$895.00
John

6/5/14
87
\$305.00
John

6/6/14
90
\$141.00
John

6/7/14
78
\$438.00
John

6/8/14
75
\$311.00
John

6/9/14
30
\$157.00
John

6/10/14
42
\$638.00
John

6/11/14
12
\$250.00
John

6/12/14
47
\$508.00
John

6/13/14
26
\$573.00
John

6/14/14
59
\$553.00
John

6/15/14
100
\$396.00
John

6/16/14
58
\$740.00
John

6/17/14
67
\$713.00
John

6/18/14
40
\$570.00
John

6/1/14
76
\$686.00
Bob

6/2/14
81
\$467.00
Bob

6/3/14
20
\$734.00
Bob

6/4/14
100
\$603.00
Bob

6/5/14
57
\$64.00
Bob

6/6/14
18
\$913.00
Bob

6/7/14
44
\$104.00
Bob

6/8/14
57
\$674.00
Bob

6/9/14
62
\$492.00
Bob

6/10/14
13
\$435.00
Bob

6/11/14
26
\$117.00
Bob

6/12/14
57
\$630.00
Bob

6/13/14
10
\$840.00
Bob

6/14/14
92
\$93.00
Bob

6/15/14
75
\$77.00
Bob

6/16/14
99
\$602.00
Bob

6/17/14
70
\$768.00
Bob

6/18/14
1
\$264.00
Bob

NBVC
2017-03-16, 01:32 PM
Try this Array Formula:

=INDEX(\$D\$2:\$D\$55,MATCH(MAX(SUMIF(\$D\$2:\$D\$55,\$D\$2:\$D\$55,\$C\$2:\$C\$55)),SUMIF(\$D\$2:\$D\$55,\$D\$2:\$D\$55,\$C\$2:\$C\$55),0))

formula must be confirmed with CTRL+SHIFT+ENTER not just ENTER

Hercules1946
2017-03-16, 03:03 PM
@NBVC
Great formula (as Usual) Although it makes no difference in this instance, shouldn't the sum Array be \$B\$2:\$B\$55 ? :)

NBVC
2017-03-16, 03:54 PM
Hi Hercules. Yes, you are correct. I thought we were looking at max dollars... but for max units sold, it would be column B.

Thanks for spotting that (and for the compliment :) )

vms777
2017-03-18, 05:39 PM
@NBVC you are an absolute saint! I spent hours trying to figure out a solution, and yours worked perfectly. Thank you so so much! @Hercules thanks for you input too.