ignoring 1 word when looking for the most common word

[Patcheen]

Hi all as i want to ignore the word "undelivered" when searching for the most common word

what i have is - =MODE(IF(A6:A1000<>unknown,A6:A1000)) (array formula)

it says #Name as a result

TIA

 

[Logit]

.
I'm not very good with formulas but does this work for your needs ?

=MODE(IF(A6:A1000<>"unknown",A6:A1000))

 

[Aladin Akyurek]

Care to post 10 rows from column A?

 

[Patcheen]

thank you so much it worked.... BUT new prob it returns blank cell as it is taking cells with formula into consideration.
my question now s how do i ignore the blank cells with formula?

 

[Logit]

.
Not tested here : https://stackoverflow.com/questions/26868704/most-frequent-word-in-a-range-ignoring-blanks

 

[Aladin Akyurek]

thank you so much it worked.... BUT new prob it returns blank cell as it is taking cells with formula into consideration.
my question now s how do i ignore the blank cells with formula?

I don't see how that could work, as MODE requires numbers and you mention "words", "undelivered", and/or "unknown" in your problem description.

 

[Patcheen]

this =INDEX(E9:E18,MODE(IF((E9:E18<>"")*ISNA(MATCH(E9:E18,$B$1:$B1,0)),MATCH(E9:E18,E9:E18,0)))) doesn't work as it includes $B$1:$B1 but i cant see what this been referenced in the post here https://stackoverflow.com/questions/26868704/most-frequent-word-in-a-range-ignoring-blanks ..... but i need to eliminate including formulas in blank cells.......

 

[Aladin Akyurek]

Would you post a 10 row fake sample with undelivered/unknown included along with the desired result or results?

 

[Patcheen]

Cant upload sample workbook to here

 

[Patcheen]

I've uploaded an example workbook to here - hope it helps

https://www.sendspace.com/file/p518kq