Formula to return most repeated text in filtered data

[mecerrato]

Hi, I have the following formulas that returns the most repeated text in a range that ignores blanks as well but I need to adjust it to only test the visible cells as the data is filtered, can someone help me?

I am not sure which is better but both seem to work, can you help me choose the best one and help me so it only looks at the filtered data (visible cells)?
=INDEX(B22:B10000,MODE(IF(B22:B10000<>"",MATCH(B22:B10000,B22:B10000,0))))
=LOOKUP(1,0/FREQUENCY(0,1/(1+COUNTIF(B22:B10000,B22:B10000))),B22:B10000)

 

[shina67]

To modify the formulas to only consider visible cells when the data is filtered, you can use the SUBTOTAL function combined with AGGREGATE function. Here's how you can adjust both formulas:

For the first formula:

=INDEX(B22:B10000,MODE(IF(SUBTOTAL(3,OFFSET(B22:B10000,ROW(B22:B10000)-MIN(ROW(B22:B10000)),,1)), IF(B22:B10000<>"", MATCH(B22:B10000, B22:B10000, 0))))

For the second formula:

=LOOKUP(1,0/FREQUENCY(0,1/(1+SUBTOTAL(3,OFFSET(B22:B10000,ROW(B22:B10000)-MIN(ROW(B22:B10000)),,1)))),B22:B10000)


These modified formulas will now consider only the visible cells when the data is filtered. As for choosing the better formula, both are effective in finding the most repeated text in a range while ignoring blanks and considering only visible cells when filtered. You can choose either based on personal preference or compatibility with your existing setup.

 

[Fluff]

@shina67 Please note Rule#17 regarding the use of AI to answer questions.

 

[Fluff]

A non volatile way

=LET(r,B22:B10000,f,FILTER(r,MAP(r,LAMBDA(m,SUBTOTAL(103,m)))),INDEX(f,MODE(XMATCH(f,f))))

 

[Domenic]

For the second formula:

=LOOKUP(1,0/FREQUENCY(0,1/(1+SUBTOTAL(3,OFFSET(B22:B10000,ROW(B22:B10000)-MIN(ROW(B22:B10000)),,1)))),B22:B10000)

The above formula does not appear to return the correct results in all cases . . .