Find the most common value if criteria met

[sharky12345]

Not sure if this is possible.....

I have a column of data between P2:P100, each cell has various words in it, each on it's own line. I'm trying to find the most common value in that column but only if the cell in column B = "Destination".

I thought perhaps a MAXIF but I have no idea where to start......

 

[Aladin Akyurek]

Row\Col	P​	Q​	R​
1​			2​
2​	london	destination	2​
3​	istanbul	destination	MOST FREQ
4​	paris		london
5​	ottowa	destination	istanbul
6​	den haag	destination
7​		destination
8​	istanbul	destination
9​	london	destination
10​	paris

In R1 control+shift+enter, not just enter:

=MAX(FREQUENCY(IF(1-($P$2:$P$10=""),IF($Q$2:$Q$10="destination",MATCH($P$2:$P$10,$P$2:$P$10,0))),ROW($P$2:$P$10)-ROW($P$2)+1))

In R2 control+shift+enter, not just enter:

=SUM(IF(FREQUENCY(IF(1-($P$2:$P$10=""),IF($Q$2:$Q$10="destination",MATCH($P$2:$P$10,$P$2:$P$10,0))),ROW($P$2:$P$10)-ROW($P$2)+1)=$R$1,1))

In R4 control+shift+enter, not just enter, and copy down:

=IF(ROWS($R$4:R4)>$R$2,"",INDEX($P$2:$P$10,SMALL(IF(FREQUENCY(IF(1-($P$2:$P$10=""),IF($Q$2:$Q$10="destination",MATCH($P$2:$P$10,$P$2:$P$10,0))),ROW($P$2:$P$10)-ROW($P$2)+1)=$R$1,ROW($P$2:$P$10)-ROW($P$2)+1),ROWS($R$4:R4))))

 

[sharky12345]

Perfect!

Thanks!!

 

[Aladin Akyurek]

Perfect!

Thanks!!

You are welcome.