Select a maximum value from a column which has repeated values

[rac241985]

Phone	Country	Count If	Launch
Iphone	Singapore	1	Apr 2017
Iphone	Singapore	2	Apr 2018
Iphone	Vietnam	1	Mar 2019
Iphone	China	1	Jun 2019
Samsung	Singapore	1	Apr 2019
Samsung	China	1	Aug 2017
Samsung	Turkey	1	Jun 2017
Samsung	Turkey	2	Jul 2018

<tbody>
</tbody>

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:

Phone	Country	Launch
Iphone	Singapore	Apr 2018
Iphone	Vietnam	Mar 2019
Iphone	China	Jun 2019

<tbody>
</tbody>

 

[Aladin Akyurek]

Phone	Country	Count If	Launch		Iphone
Iphone	Singapore	1	Apr-17		3
Iphone	Singapore	2	Apr-18		country	launch
Iphone	Vietnam	1	Mar-19		Singapore	Apr-18
Iphone	China	1	Jun-19		Vietnam	Mar-19
Samsung	Singapore	1	Apr-19		China	Jun-19
Samsung	China	1	Aug-17
Samsung	Turkey	1	Jun-17
Samsung	Turkey	2	Jul-18

<tbody>
</tbody>

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.