Frequency Formula for Greatest Occurrence by Date

[rick60]

Have 2 columns of data - salesman# and date of sale.
In column 3 need to identify the salesman# that had the most sales for that date.
Would prefer that the salesman# be identified only once for the date that the salesman# had the most sales
(similar to the third column of the example) and have all other column cells blank for that date so the data can be pivoted.

Salesman# Date of Sale Most Sales per Date
Salesman 1 1/1/2013 Salesman 2
Salesman 1 1/1/2013
Salesman 2 1/1/2013
Salesman 2 1/1/2013
Salesman 2 1/1/2013
Salesman 2 1/1/2013
Salesman 3 1/2/2013 Salesman 3
Salesman 3 1/2/2013
Salesman 1 1/2/2013
Salesman 3 1/2/2013
Salesman 1 1/3/2013 Salesman 2
Salesman 2 1/3/2013
Salesman 2 1/3/2013
Salesman 2 1/3/2013
Salesman 3 1/3/2013

Have tried the following but it keeps returning all salesmen:
=IF(COUNTIFS($A$2:A2,A2,$B$2:B2,B2)=MAX(FREQUENCY(IF($A$2:$A$16<>"",IF($B$2:$B$16=B2,MATCH($A$2:$A$16,$A$2:$A$16,0))),ROW($A$2:$A$8)-ROW($A$2)+1)),A2,"")

Any help/guidance is appreciated.

Rick

[XOR LX]

Hi,

Try this array formula:

=IF(ROWS($A$2:$A2)=MATCH(C2,$C$2:$C$16,0),INDEX($A$2:$A$16,MATCH(MAX(SUMIFS($B$2:$B$16,$C$2:$C$16,C2,$A$2:$A$16,$A$2:$A$16)),SUMIFS($B$2:$B$16,$C$2:$C$16,C2,$A$2:$A$16,$A$2:$A$16),0)),"")

Regards

[rick60]

Appeciate the effort XOR LX. Am getting a circular reference when the formula is setup in column C.

Regards,
Ron

[FDibbins]

Hi and welcome to the forum

try to put it in another column, 1 that isnt used in that formula

the circ ref is probably because C is referenced within that formula...MATCH(C2,$C$2:$C$16,0