Find the most frequent name within a range

[DougStroud]

I would like to find the most frequent name in a range; however, I think it is a little complicated.

To help make sense of this- this list contains names from an awards ceremony for writers, advertisers and photographers. I would like to see which photographer garnered the most wins.

The challenge is that sometimes the cells are combined with multiple names, a writer and a photographer's name. I don't know if that impacts the search...


Range: C1:C900 (contains multiple words, but the word picture or story are the key words)
Range: D1:D900 (Lists First, Second or Third Place)
Range: E1:E900 (contains the photographer's name but also could contain the writer's name)

 

[Cubist]

Do you have a list of photographers already? If not, how can you tell who is a photographer in the text string?

 

[DougStroud]

Do you have a list of photographers already? If not, how can you tell who is a photographer in the text string?

There is a list of photographers. Initially I was thinking of using Index, Mode, Match but I think with some cells having two names in there it might not pick up the individual name. For example, there is the name Paige Diegle for an award but in a following cell there is Paige Diegle, Janice Ross.

 

[Cubist]

I have the list of photographers in H, adapt the ranges. Try:

Book1
	C	D	E	F	G	H	I
1	Pictorial Photo	First Place	Paige Dingler			Paige Dingler	1
2	Slideshow or Gallerly	First Place	Paige Dingler			Lee Luther, Jr.	0
3	Sports News Photo	First Place	Lee Luther, Jr.			Justin Faulconer	1
4	Story	Second Place	Justin Faulconer			Nicky Marshok	2
5	Horror Story	Second Place	Judy Harbin, Nicky Marshok
6	Pictorial Photo	First Place	Judy Harbin, Nicky Marshok
Sheet6

Cell Formulas
Range	Formula
I1:I4	I1	=LET( c,ISNUMBER(SEARCH("pict",$C$1:$C$6))+ISNUMBER(SEARCH("story",$C$1:$C$6)), d,($D$1:$D$6="First Place")+($D$1:$D$6="Second Place")+($D$1:$D$6="Third Place"), e,ISNUMBER(SEARCH(H1,$E$1:$E$6)), IFERROR(ROWS(FILTER($C$1:$C$6,c*d*e)),0))