SOLVED

Need help with Countif Filter formulae

New Contributor

Hello Community, I'm looking for a formulae to find the top 4 car brand preferred by Electric Vehicle type? I can use pivot for the same however, I'm looking for a single line formula, I've attached the file

1. Which are top 4 car brand preferred by Electric Vehicle type
2. What % of the respondents belong to the 25-34 age group (age)?
3. How often do the majority of people use Vehicle (Frequency

 

IDGenderAgeFrequencyVehicle  typeBrand
1male45mostlyPetrolBMW
2female25mostlyDiesel Toyota
3female42mostlyManualWV
4female35mostlyElectricWV
5female62mostlyElectricWV
6female43rarelyElectricWV
7female47mostlyElectricBMW
8female57mostlyManualMini
9male36mostlyElectricMini
10female37rarelyElectricBMW
11female42mostlyElectricWV
12male37mostlyManualother
13female25mostlyCNGWV
14male42mostlyElectricBMW
15female35rarelyManualWV
16female62mostlyManualMini
17female43sometimesElectricBMW
18female47mostlyElectricWV
19female57rarelyotherBMW
20female36sometimesManualWV
21female37sometimesElectricMini
22male42sometimesElectricBMW
23female37sometimesElectricBMW
24female36sometimesElectricWV
25female43rarelyElectricMini
26male51mostlyElectricBMW
27female46mostlyElectricWV
28female45sometimessensitiveother
29male50mostlyElectricWV
30female49mostlyElectricWV
31female46mostlyElectricBMW
32female40rarelyManualBMW
33female42mostlyElectricMini
34male67mostlyElectricWV
35female30rarelysensitiveBMW
36female58rarelyManualWV
37female48mostlyManualWV
38female59mostlyElectricWV
39female44mostlyElectricWV
40female46mostlyElectricWV
41female45mostlyElectricWV
42female25rarelyElectricWV
43male42rarelyElectricBMW
44female37mostlyElectricBMW
45female40mostlyPetrolBMW
46female37mostlyManualother
47female31mostlyElectricBMW
48female58mostlyElectricBMW
49female31mostlyElectricWV
      
      
      
      



2 Replies
best response confirmed by Hans Vogelaar (MVP)
Solution

Hi @ZakiNuman 

 

You did not mention the Excel version you run (asked in Welcome to your Excel discussion space!). I assumed 365

 

Sample.png

 

#1 top 4 car brand preferred by Electric

=LET(
  elect, FILTER(TableCar[Brand],TableCar[Vehicle  type]="electric"),
  xmt,   XMATCH(elect,elect),
  freq,  FREQUENCY(xmt,xmt),
  TAKE(UNIQUE(SORTBY(elect, INDEX(freq,SEQUENCE(ROWS(elect))),-1)), 4)
)

 

 

#2 % of the respondents belong to the 25-34

 

=COUNTIFS(TableCar[Age], ">=" & 25, TableCar[Age], "<=" & 34) / COUNT(TableCar[Age])

 

#3 How often do the majority of people use Vehicle (what I understood...)

 

=LET(
  freq, COUNTIF(TableCar[Frequency],TableCar[Frequency]),
  x,    MAX(freq),
  CHOOSE({1,2}, XLOOKUP(x,freq,TableCar[Frequency]), x)
)

 

 

Thank you so much for you help. Much appreciated! have a lovey day