Forum Discussion
Solved
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 ...
Hi ZakiNuman
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) )
Hi ZakiNuman
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)
)
Thank you so much for you help. Much appreciated! have a lovey day