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.