adding additional condition in calculating 5 most frequent string values

Dear forums members. I need your help in upgrading a formula that I use to calculate 5 most frequent string values. I use this formula on a database that has two groups of participants. Now I need to make the same calculation for group 1 only, which is probably requires adding additional IF into the formula. I couldn't figure out how to make this work. Your help would be very much appreciated.
A demo file is attached. I have to calculate into "asylum seekers only" sheet. The total for all participants is calculated in "calculation" sheet.
Thank you

Marina

If Excel in Office 365 ProPlus handles spilled formulas, dynamic arrays, and includes the newer worksheet functions, you should be able to use a single formula to populate calculation!D3:I3, namely

=INDEX(UNIQUE(SORTBY(country,COUNTIFS(country,country),-1)),SEQUENCE(1,6))

and calculation!J3:O3 using

=INDEX(UNIQUE(SORTBY(language5,COUNTIFS(language5,language5,language5,"<>other"),-1)),SEQUENCE(1,6))

The latter is longer because you seem to exclude other, which is more numerous than Pashtu or Arabic.

To add a condition for only asylum seekers, add to the COUNTIFS calls' conditions.

'asylum seekers only'!D3:I3:
=INDEX(UNIQUE(SORTBY(country,COUNTIFS(country,country,data!$D$4:$D$149,"Asylum Seeker"),-1)),SEQUENCE(1,6))

'asylum seekers only'!J3:O3:
=INDEX(UNIQUE(SORTBY(language5,COUNTIFS(language5,language5,language5,"<>other",data!$D$4:$D$149,"Asylum Seeker"),-1)),SEQUENCE(1,6))

Hi hringrv
Thank you for your help
I was not able to make your suggested formula work. It returns NAME.
Using the formula in file, I was trying to add the argument : data!$D$4:$D$149,"Asylum Seeker" to calculate D4:
=COUNTIF(country,D3,data!$D$4:$D$149,"Asylum Seeker") - it returns "you have too many arguments"
Any ideas how to resolve this?
Regards
Marina

Originally Posted by Marina Kunin

. . . I was not able to make your suggested formula work. It returns NAME.

What does =INFO("Version") return? What does =SORTBY({2;1},{1;2},-1) return?

. . . I was trying to add the argument : data!$D$4:$D$149,"Asylum Seeker" to calculate D4:

=COUNTIF(country,D3,data!$D$4:$D$149,"Asylum Seeker")

. . . it returns "you have too many arguments"

Use COUNTIFS rather than COUNTIF.

Thank you hringrv
CONTIFS solves the problem in D4:I4, but because D3:I3 are incorrect, as they are calculated for the whole group, not only asylum seekers, the calculation is wrong

Please see this workbook on OneDrive.

Thanks hringrv
I do not know what is the problem, but it does not work for me. Please see the attached file - the formula returns NAME?
Regards
Marina

I'll look at your file, but what does =INFO("release") return on your system? I want to make sure what features you have.

OK, simpler and avoiding any functions which aren't in Excel 2010.

I'd add supplemental calculations in the data worksheet.

O4: =MATCH(E4,country,0)
P4: =MATCH(F4,language5,0)/(F4<>"other")
R4: =MATCH(E4,country,0)/(D4="Asylum Seeker")
S4: =MATCH(F4,language5,0)/(F4<>"other")/(D4="Asylum Seeker")

Select O4:S4 and fill down into O5:S149. These return index of first match for country or language for the country or language on a given row and errors for blank cells or failing to meet inclusion criteria. ERROR RETURN VALUES ARE INTENTIONAL.

Then in the calculation worksheet,

D3: =INDEX(country,MODE(IFERROR(data!$O$4:$O$149,FALSE)))
E3: =INDEX(country,MODE(IFERROR(data!$O$4:$O$149/(1-SIGN(COUNTIF($D3:D3,country))),FALSE)))

Fill E3 right into F3:I3.

J3: =INDEX(language5,MODE(IFERROR(data!$P$4:$P$149,FALSE)))
K3: =INDEX(language5,MODE(IFERROR(data!$P$4:$P$149/(1-SIGN(COUNTIF($J3:J3,language5))),FALSE)))

Fill K3 right into L3:O3.

Then on the asylum seekers only worksheet

D3: =INDEX(country,MODE(IFERROR(data!$R$4:$R$149,FALSE)))
E3: =INDEX(country,MODE(IFERROR(data!$R$4:$R$149/(1-SIGN(COUNTIF($D3:D3,country))),FALSE)))

Fill E3 right into F3:I3.

J3: =INDEX(language5,MODE(IFERROR(data!$S$4:$S$149,FALSE)))
K3: =INDEX(language5,MODE(IFERROR(data!$S$4:$S$149/(1-SIGN(COUNTIF($J3:J3,language5))),FALSE)))

Fill K3 right into L3:O3.

The formulas in calculation and asylum seekers only worksheets may all need to be entered as array formulas if your version of Excel doesn't support spilled formulas.

thank you
Marina

Thank you kvsrinivasamurthy, this is very helpful. Can you just explain what you did in A2?
Thanks
Marina

Ooh! I figured it out. This is clever ! Thank you kvsrinivasamurthy

Dear forums members. I have to reopen this thread. I need your help in upgrading the formula that I use to calculate 5 most frequent string values. I want to exclude "other" value from the calculation. I couldn't figure out how to make this work. Your help would be very much appreciated.
A demo file is attached. Here is the formula:
=INDEX(country,MODE(IF((country<>"")*(COUNTIF($A$2,Refugee)>0)*ISNA(MATCH(country,$C$3:C$3,0)),MATCH(country,country,0) * {1,1})))

Yes, it works! Thank you so much JeteMc