Most frequently occurring visible text string in a filtered column

[nibor]

This formula returns #NA if the filtered list only contains one row:

=INDEX(B2:B10,MODE(IF(SUBTOTAL(3,OFFSET(B2:B10,ROW(B2:B10)-ROW(B2),0,1))>0,MATCH(B2:B10,B2:B10,0))))

(Sourced from: Find most frequently occurring text string in a VISIBLE or filtered)

Can anyone help with a formula that returns the most frequent value regardless of how many rows are visible after filtering?

Thanks!

 

[Eric W]

Welcome to the MrExcel forum!

Try:

=INDEX(B2:B10,MODE(IF(SUBTOTAL(3,OFFSET(B2:B10,ROW(B2:B10)-ROW(B2),0,1))>0,MATCH(B2:B10,B2:B10,0)*{1,1})))

 

[nibor]

Just for info: my digging suggests that IFERROR might be helpful, but what to use if there is an error is throwing me. I've tried this but it only returns the first value in the array:
=INDEX(B2:B10,MIN(IF(SUBTOTAL(3,OFFSET(B2,ROW(B2:B10)-ROW(B2),0)),ROW(B2:B10)-ROW(B2:B10)+1)))

 

[nibor]

Oh thanks so much Eric W - that works!!

 

[Eric W]

Happy to help!

 

[ninjapaping]

Welcome to the MrExcel forum!

Try:

=INDEX(B2:B10,MODE(IF(SUBTOTAL(3,OFFSET(B2:B10,ROW(B2:B10)-ROW(B2),0,1))>0,MATCH(B2:B10,B2:B10,0)*{1,1})))

hello @Eric W what is the formula for the 2nd and 3rd in the rank?

 

[Eric W]

Maybe this would work for you:

Book1
	A	B	C	D
1			Most common visible
2			Banana	5
3			Cherry	4
4			Apple	2
5			Grape	2
6			Pomelo	1
7			Tangerine	1
8			Orange	0
9
10	List
11	Apple
12	Banana
14	Banana
15	Cherry
17	Cherry
18	Apple
21	Pomelo
22	Banana
23	Banana
24	Cherry
25	Grape
26	Grape
27	Cherry
28	Banana
30	Tangerine
31
Sheet2

Cell Formulas
Range	Formula
C2:D8	C2	=LET(rng,A11:A30,rws,ROWS(rng),u,UNIQUE(rng),v,SUBTOTAL(103,OFFSET(rng,SEQUENCE(rws,,0),0,1)),arr,(TRANSPOSE(u)=rng)*v,freq,TRANSPOSE(MMULT(SEQUENCE(,rws,,0),arr)),tbl,CHOOSE({1,2},u,freq),s,SORT(tbl,2,-1),s)
Dynamic array formulas.

This formula creates a list of the unique values in the table, counts how many rows are visible for each value, then sorts the unique list by that number.

 

[ninjapaping]

Maybe this would work for you:

Book1
	A	B	C	D
1			Most common visible
2			Banana	5
3			Cherry	4
4			Apple	2
5			Grape	2
6			Pomelo	1
7			Tangerine	1
8			Orange	0
9
10	List
11	Apple
12	Banana
14	Banana
15	Cherry
17	Cherry
18	Apple
21	Pomelo
22	Banana
23	Banana
24	Cherry
25	Grape
26	Grape
27	Cherry
28	Banana
30	Tangerine
31
Sheet2

Cell Formulas
Range	Formula
C2:D8	C2	=LET(rng,A11:A30,rws,ROWS(rng),u,UNIQUE(rng),v,SUBTOTAL(103,OFFSET(rng,SEQUENCE(rws,,0),0,1)),arr,(TRANSPOSE(u)=rng)*v,freq,TRANSPOSE(MMULT(SEQUENCE(,rws,,0),arr)),tbl,CHOOSE({1,2},u,freq),s,SORT(tbl,2,-1),s)
Dynamic array formulas.

This formula creates a list of the unique values in the table, counts how many rows are visible for each value, then sorts the unique list by that number.

it works fine. but i need each result separately to be used as reference in my next formula

 

[Eric W]

Just use an index into the sorted table (the "s" name):

Book1
	A	B	C	D
1			Most common visible	Index
2			Banana	1
3			Cherry	2
4			Grape	3
5
6
7
8
9
10	List
11	Apple
12	Banana
14	Banana
15	Cherry
17	Cherry
18	Apple
21	Pomelo
22	Banana
23	Banana
24	Cherry
25	Grape
26	Grape
27	Cherry
28	Banana
30	Tangerine
31
Sheet2

Cell Formulas
Range	Formula
C2:C4	C2	=LET(rng,A11:A30,ix,D2,rws,ROWS(rng),u,UNIQUE(rng),v,SUBTOTAL(103,OFFSET(rng,SEQUENCE(rws,,0),0,1)),arr,(TRANSPOSE(u)=rng)*v,freq,TRANSPOSE(MMULT(SEQUENCE(,rws,,0),arr)),tbl,CHOOSE({1,2},u,freq),s,SORT(tbl,2,-1),INDEX(s,ix,1))

Also be aware that in case of ties, you'll only get one value.

 

[ninjapaping]

Just use an index into the sorted table (the "s" name):

Book1
	A	B	C	D
1			Most common visible	Index
2			Banana	1
3			Cherry	2
4			Grape	3
5
6
7
8
9
10	List
11	Apple
12	Banana
14	Banana
15	Cherry
17	Cherry
18	Apple
21	Pomelo
22	Banana
23	Banana
24	Cherry
25	Grape
26	Grape
27	Cherry
28	Banana
30	Tangerine
31
Sheet2

Cell Formulas
Range	Formula
C2:C4	C2	=LET(rng,A11:A30,ix,D2,rws,ROWS(rng),u,UNIQUE(rng),v,SUBTOTAL(103,OFFSET(rng,SEQUENCE(rws,,0),0,1)),arr,(TRANSPOSE(u)=rng)*v,freq,TRANSPOSE(MMULT(SEQUENCE(,rws,,0),arr)),tbl,CHOOSE({1,2},u,freq),s,SORT(tbl,2,-1),INDEX(s,ix,1))

Also be aware that in case of ties, you'll only get one value.

thank you @Eric W