Select a maximum value from a column which has repeated values

[rac241985]

[TABLE="class: cms_table, width: 240"]
<tbody>[TR]
[TD]Phone[/TD]
[TD]Country[/TD]
[TD]Count If[/TD]
[TD]Launch[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Iphone[/TD]
[TD]Singapore[/TD]
[TD]1[/TD]
[TD]Apr 2017[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Iphone[/TD]
[TD]Singapore[/TD]
[TD]2[/TD]
[TD]Apr 2018[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Iphone[/TD]
[TD]Vietnam[/TD]
[TD]1[/TD]
[TD]Mar 2019[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Iphone[/TD]
[TD]China[/TD]
[TD]1[/TD]
[TD]Jun 2019[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Samsung[/TD]
[TD]Singapore[/TD]
[TD]1[/TD]
[TD]Apr 2019[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Samsung[/TD]
[TD]China[/TD]
[TD]1[/TD]
[TD]Aug 2017[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Samsung[/TD]
[TD]Turkey[/TD]
[TD]1[/TD]
[TD]Jun 2017[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Samsung[/TD]
[TD]Turkey[/TD]
[TD]2[/TD]
[TD]Jul 2018
[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


Count if Formula used: COUNTIF($B$1:B1,B1)

I have 4 columns as shown above. In column B, the country maybe repeated twice. I tried to use the count if function as shown above. I am not sure how to extract the maximum number and show the output based on that as follows if the user selects Iphone. I know how to extract the first time the value occurs (i.e. when Output is "1"). Not sure how to extract it for the maximum number. Can someone please help


Desired Output:
[TABLE="class: cms_table, width: 240"]
<tbody>[TR]
[TD]Phone[/TD]
[TD]Country[/TD]
[TD]Launch[/TD]
[/TR]
[TR]
[TD]Iphone[/TD]
[TD]Singapore[/TD]
[TD="align: right"]Apr 2018[/TD]
[/TR]
[TR]
[TD]Iphone[/TD]
[TD]Vietnam[/TD]
[TD="align: right"]Mar 2019[/TD]
[/TR]
[TR]
[TD]Iphone[/TD]
[TD]China[/TD]
[TD="align: right"]Jun 2019[/TD]
[/TR]
</tbody>[/TABLE]

 

[Aladin Akyurek]

[TABLE="class: grid, width: 573"]
<tbody>[TR]
[TD]Phone[/TD]
[TD]Country[/TD]
[TD]Count If[/TD]
[TD]Launch[/TD]
[TD][/TD]
[TD]Iphone[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Iphone[/TD]
[TD]Singapore[/TD]
[TD]1[/TD]
[TD]Apr-17[/TD]
[TD][/TD]
[TD="align: right"]3[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Iphone[/TD]
[TD]Singapore[/TD]
[TD]2[/TD]
[TD]Apr-18[/TD]
[TD][/TD]
[TD]country[/TD]
[TD]launch[/TD]
[/TR]
[TR]
[TD]Iphone[/TD]
[TD]Vietnam[/TD]
[TD]1[/TD]
[TD]Mar-19[/TD]
[TD][/TD]
[TD]Singapore[/TD]
[TD="align: right"]Apr-18[/TD]
[/TR]
[TR]
[TD]Iphone[/TD]
[TD]China[/TD]
[TD]1[/TD]
[TD]Jun-19[/TD]
[TD][/TD]
[TD]Vietnam[/TD]
[TD="align: right"]Mar-19[/TD]
[/TR]
[TR]
[TD]Samsung[/TD]
[TD]Singapore[/TD]
[TD]1[/TD]
[TD]Apr-19[/TD]
[TD][/TD]
[TD]China[/TD]
[TD="align: right"]Jun-19[/TD]
[/TR]
[TR]
[TD]Samsung[/TD]
[TD]China[/TD]
[TD]1[/TD]
[TD]Aug-17[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Samsung[/TD]
[TD]Turkey[/TD]
[TD]1[/TD]
[TD]Jun-17[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Samsung[/TD]
[TD]Turkey[/TD]
[TD]2[/TD]
[TD]Jul-18[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

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

=SUM(IF(FREQUENCY(IF(1-($B$2:$B$9=""),IF($A$2:$A$9=F$1,MATCH($B$2:$B$9,$B$2:$B$9,0))),ROW($B$2:$B$9)-ROW($B$2)+1),1))

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

=IF(ROWS($F$4:F4)>$F$2,"",INDEX($B$2:$B$9,SMALL(IF(FREQUENCY(IF(1-($B$2:$B$9=""),IF($A$2:$A$9=F$1,MATCH($B$2:$B$9,$B$2:$B$9,0))),ROW($B$2:$B$9)-ROW($B$2)+1),ROW($B$2:$B$9)-ROW($B$2)+1),ROWS($F$4:F4))))

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

=IF($F4="","",MAX(IF($A$2:$A$9=$F$1,IF($B$2:$B$9=$F4,$D$2:$D$9))))

If you have MAXIFS available on your system, you can invoke in G4 which needs just enter...

=IF($F4="","",MAXIFS($D$2:$D$9,$A$2:$A$9,$F$1,$B$2:$B$9,$F4))

and copy down.