Find most occurring text within a Table column

[XxCMoneyxX]

Hello,

I have a table that I need to list the most occurring text within a column. I always use the formula below with great results, but I now need this formula to recalculate when I apply filters to my table.
(My column is named "Company")

Here is my standard formula that I use for most occurring text:

=INDEX(A1:A100,MODE(IF(A1:A100<>0,MATCH(A1:A100,A1:A100,0)))) 

So I changed the cell range to "Table2[Company]" hit Ctrl + Shift + Enter. But unfortunately the formula is not recalculating when I apply filters to the table. It is still finding the most frequent text for the entire column table range.

Does anyone have any suggestions? I am probably missing something obvious here...


Thanks!
-Carl

[dilipandey]

Hi Carl,

Did you changed all the 4 ranges in your formula?
Suggest you to upload the sample file if you still face any issue. thanks.

Regards,
DILIPandey
<click on below * if this helps>

[JosephP]

there's nothing in that formula that will respond to filters. you need something like (untested as I'm on my cell)

=INDEX(A1:A100,MODE(IF(A1:A100<>0,MATCH(if(subtotal(3,offset(A1:A100,row(a1:a100)-1,0,1)) a1:a100),A1:A100,0)))))

[Ron Coderre]

To use table references, try this ARRAY FORMULA, completed by holding down CTRL and SHIFT when you press ENTER (instead of just pressing ENTER)

Please Login or Register  to view this content.

Is that something you can work with?

[XxCMoneyxX]

Hi Ron,

This formula gives me a Value Not Available Error.

[XxCMoneyxX]

Ron,

I spoke too soon. I tried it on a different spreadsheet and it works great.

I still receive an error if my filter creates too small of a list. ?

[Ron Coderre]

I only get the error when the filtered list has only one item in it or when no item has more than one instance. I'll see if I can figure out why that happens.