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)

=INDEX(Table1[Company],MODE(IF(SUBTOTAL(3,OFFSET(Table1[[#Headers],[Company]],ROW(Table1[Company])-1,)),MATCH(Table1[Company],Table1[Company],0))))

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.