Need help with Countif Filter formulae

Question

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

lorenzo · Accepted Answer

Hi&nbsp;ZakiNuman&nbsp;&nbsp;You did not mention the Excel version you run (asked in Welcome to your Excel discussion space!). I assumed 365&nbsp;&nbsp;#1&nbsp;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)
)&nbsp;&nbsp;#2 % of the respondents belong to the 25-34&nbsp;=COUNTIFS(TableCar[Age], "&gt;=" &amp; 25, TableCar[Age], "&lt;=" &amp; 34) / COUNT(TableCar[Age])&nbsp;#3&nbsp;How often do the majority of people use Vehicle (what I understood...)&nbsp;=LET(
  freq, COUNTIF(TableCar[Frequency],TableCar[Frequency]),
  x,    MAX(freq),
  CHOOSE({1,2}, XLOOKUP(x,freq,TableCar[Frequency]), x)
)&nbsp;&nbsp;

Forum Discussion

Need help with Countif Filter formulae

Share

Resources