MODE for Text including Count IF...HELP!

[apatheticrory]

****** id="cke_pastebin" style="position: absolute; top: 0px; width: 1px; height: 1px; overflow: hidden; left: -1000px;">
[TABLE="class: cms_table_grid"]
<tbody>[TR]
[TD][/TD]
[TD]Year[/TD]
[TD]Q1[/TD]
[TD]April[/TD]
[/TR]
[TR]
[TD]Busiest Team[/TD]
[TD]A[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Most Common Nat[/TD]
[TD]B[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Most Common Nat exc GBR[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Most Common Nat (Alpha Team)[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Most Common Nat exc GBR (Alpha Team)[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Most Common Nat (Bravo Team)[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Most Common Nat exc GBR (Bravo Team)[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Most Common Nat (Charlie Team)[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Most Common Nat exc GBR (Charlie Team)[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


The Cell in which i have entered A contains the following formula:

Code:
=INDEX(Data!D2:D20,MATCH(MAX(COUNTIF(Data!D2:D20,Data!D2:D20)),COUNTIF(Data!D2:D20,Data!D2:D20),0))
The cell in which i have entered B contains the same but with the Column changed to incorporate the Nationality not the Team.

The data on which this is produced is a simple sheet as follows:

[TABLE="class: cms_table_grid"]
<tbody>[TR]
[TD]Date[/TD]
[TD]Quarter[/TD]
[TD]Month[/TD]
[TD]Team[/TD]
[TD]Nationality[/TD]
[/TR]
[TR]
[TD]01/01/2017[/TD]
[TD]4[/TD]
[TD]1[/TD]
[TD]Alpha[/TD]
[TD]GBR[/TD]
[/TR]
[TR]
[TD]01/04/2017[/TD]
[TD]1[/TD]
[TD]4[/TD]
[TD]Charlie[/TD]
[TD]USA[/TD]
[/TR]
</tbody>[/TABLE]

</body>Hopefully someone can help me fill in the blank cells in the below table? I am trying to create a sheet to collate application stats for a few teams....


[TABLE="class: cms_table_grid"]
<tbody>[TR]
[TD][/TD]
[TD]Year[/TD]
[TD]Q1[/TD]
[TD]April[/TD]
[/TR]
[TR]
[TD]Busiest Team[/TD]
[TD]A[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Most Common Nat[/TD]
[TD]B[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Most Common Nat exc GBR[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Most Common Nat (Alpha Team)[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Most Common Nat exc GBR (Alpha Team)[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Most Common Nat (Bravo Team)[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Most Common Nat exc GBR (Bravo Team)[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Most Common Nat (Charlie Team)[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Most Common Nat exc GBR (Charlie Team)[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


The Cell in which i have entered A contains the following formula:

[COLOR=#333333]=INDEX(Data!D2:D20,MATCH(MAX(COUNTIF(Data!D2:D20,Data!D2:D20)),COUNTIF(Data!D2:D20,Data!D2:D20),0))[/COLOR]

The cell in which i have entered B contains the same but with the Column changed to incorporate the Nationality not the Team.


The data on which this is produced is a simple sheet as follows:

[TABLE="class: cms_table_grid"]
<tbody>[TR]
[TD]Date[/TD]
[TD]Quarter[/TD]
[TD]Month[/TD]
[TD]Team[/TD]
[TD]Nationality[/TD]
[/TR]
[TR]
[TD]01/01/2017[/TD]
[TD]4[/TD]
[TD]1[/TD]
[TD]Alpha[/TD]
[TD]GBR[/TD]
[/TR]
[TR]
[TD]01/04/2017[/TD]
[TD]1[/TD]
[TD]4[/TD]
[TD]Charlie[/TD]
[TD]USA[/TD]
[/TR]
</tbody>[/TABLE]

 

[Aladin Akyurek]

Try to post input which correlates to the output that is desired. Try also to avoid describing the problem to solve by means of Excel formulas.