Find date of max occurrence of name from table

[eggman9]

I am looking for a formula to calculate the date that a sales person makes the most sales. I have a huge table that looks similar to this:

Sale Date	Name
1/5/22	Sam
1/5/22	Frank
1/6/22	Bob
1/7/22	Frank
1/7/22	Frank
1/7/22	Frank

I want to enter the name I am interested in finding, and it would output the date that the person sold the most. In this example, if I were interested in Frank, it would output 1/7/22 because that is the date that his name appears the most.

Please let me know if this requires further clarification, thanks!

 

[Fluff]

Hi & welcome to MrExcel.
Feel sure there must be a simpler way, but how about

Fluff.xlsm
	A	B	C	D	E
1	Sale Date	Name
2	01/05/2022	Sam		Frank	01/07/2022
3	01/05/2022	Frank
4	01/06/2022	Bob
5	01/07/2022	Frank
6	01/07/2022	Frank
7	01/07/2022	Frank
8
9
10
Data

Cell Formulas
Range	Formula
E2	E2	=LET(u,UNIQUE(FILTER(A2:A20,B2:B20=D2)),c,COUNTIFS(A2:A20,u,B2:B20,D2),FILTER(u,c=MAX(c)))

 

[eggman9]

Hello and thank you for your reply.

Unfortunately in practice this appears to yield a range of dates, and if I use control-shift-enter, it simply displays the first date the name appears. Not sure exactly why, I have never used Let so I don't know what the output should be

 

[Fluff]

In that case can you post some data that shows the problem.

 

[Fluff]

Could you have multiple dates that appear the same number of times? If so what should happen?

 

[Eric W]

How about:

=MODE(FILTER(A2:A7,B2:B7=D2))

If you want to list multiple dates that appear the same number of times:

=MODE.MULT(FILTER(A2:A7,B2:B7=D2))

 

[eggman9]

These solutions all work when I try them in a test file, but I am still having trouble implementing them in my live file. Maybe this is because I am referencing a power query that is merging several files together on a separate sheet from where I am trying to display the dates?

 

[Fluff]

In what way don't they work?

 

[eggman9]

Various errors, the latest says Value not Available Error

 

[Fluff]

Check that your dates are real dates & not text.