SOLVED

New Contributor

# Need help with Countif Filter formulae

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

 ID Gender Age Frequency Vehicle  type Brand 1 male 45 mostly Petrol BMW 2 female 25 mostly Diesel Toyota 3 female 42 mostly Manual WV 4 female 35 mostly Electric WV 5 female 62 mostly Electric WV 6 female 43 rarely Electric WV 7 female 47 mostly Electric BMW 8 female 57 mostly Manual Mini 9 male 36 mostly Electric Mini 10 female 37 rarely Electric BMW 11 female 42 mostly Electric WV 12 male 37 mostly Manual other 13 female 25 mostly CNG WV 14 male 42 mostly Electric BMW 15 female 35 rarely Manual WV 16 female 62 mostly Manual Mini 17 female 43 sometimes Electric BMW 18 female 47 mostly Electric WV 19 female 57 rarely other BMW 20 female 36 sometimes Manual WV 21 female 37 sometimes Electric Mini 22 male 42 sometimes Electric BMW 23 female 37 sometimes Electric BMW 24 female 36 sometimes Electric WV 25 female 43 rarely Electric Mini 26 male 51 mostly Electric BMW 27 female 46 mostly Electric WV 28 female 45 sometimes sensitive other 29 male 50 mostly Electric WV 30 female 49 mostly Electric WV 31 female 46 mostly Electric BMW 32 female 40 rarely Manual BMW 33 female 42 mostly Electric Mini 34 male 67 mostly Electric WV 35 female 30 rarely sensitive BMW 36 female 58 rarely Manual WV 37 female 48 mostly Manual WV 38 female 59 mostly Electric WV 39 female 44 mostly Electric WV 40 female 46 mostly Electric WV 41 female 45 mostly Electric WV 42 female 25 rarely Electric WV 43 male 42 rarely Electric BMW 44 female 37 mostly Electric BMW 45 female 40 mostly Petrol BMW 46 female 37 mostly Manual other 47 female 31 mostly Electric BMW 48 female 58 mostly Electric BMW 49 female 31 mostly Electric WV

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

# Re: Need help with Countif Filter formulae

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

#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)
)``````

# Re: Need help with Countif Filter formulae

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