Help for formula for extracting different words and sorting them among spreadsheet

[bobo_kbg]

Hello all,

I need a specific formula. I've search a lot on internet, found way more complicated requests but not this one.

I will be typing different words in different cells and columns. Something like this:
B2: Home
C4: Contacts
C3 Home
H5: Prices

There will be hundreds of words, most of them will  the same. Also some cells might be empty.

I need a formula that will tell me all the words I've used, how many times I used them and sort them by how many times I've used them.

It will be great If the formula can do this among a whole excel document - between different spreadsheets. But even for one spreadsheet will be OK.

Thanks in advance. 

[#2]

@bobo_kbg 

It depends on which Excel version/platform you are. And in any case it's better to define the range in which to search, otherwise you could wait recalculation forever.

[#3]

@Sergei Baklan

I am using 

Microsoft® Excel® for Microsoft 365 MSO (Version 2112 Build 1) 64-bit

It is not a problem to define 15-20 words that I will use. After filling all the data I will know that I've used this, this and this word and I will be able to pre-define them in the final formula.

Attached is a photo of what I want to achieve

[#4]

If you are going to predefine the names that would be used, then here is what I would do for each defined name:
COUNTIF($B$2:$F$12,B28)

If you didn't define names, then I would use a dynamic list to display the unique names and then apply the above formulas to show the count of reoccurrence:
UNIQUE($B$2:$F$12) # to generate a unique list
COUNTIF($B$2:$F$12,B28) # to display the count

[#5]

@NowshadAhmedThanks. I've tried it but it gave an error. I am attaching a preview. I wanted to attach the demo file, but there isn't option to do so here.

[#6]

The B28 in my formula (COUNTIF($B$2:$F$12,B28)) is the reference cell for the defined names.

In your case it would be B17 instead of B28
COUNTIF($B$2:$F$12,B17) # Use this formula

[#7]

I think I've made it work :) Thanks

[#8]

@bobo_kbg 

If name the source as range

that could be

=LET(
   n,     ROWS(range),
   m,     COLUMNS(range),
   k,     SEQUENCE(n*m),
   rN,    INT( (k-1)/m)+1,
   cN,    MOD(k-1,m)+1,
   all,   LET( t, INDEX( range, rN, cN), FILTER(t, t<>"")),
   list,  UNIQUE(all),
   count, MMULT(--(list=TRANSPOSE(all)), SEQUENCE(ROWS(all),,1,0) ),
   IF({1,0}, list, count)  )

[#9]

Where you put this code?

[#10]

@bobo_kbg 

In formula bar