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

 

[Patcheen]

Using MODE.MULT...

In F9 control+shift+enter, not just enter, and copy down:

=IFERROR(INDEX($E$9:$E$31,INDEX(MODE.MULT(IF(ISTEXT($E$9:$E$31),IF(ISNA(MATCH($E$9:$E$31,{"","unknown"},0)),MATCH($E$9:$E$31,$E$9:$E$31,0)))),ROWS($P$2:$P2))),"")

whats in p2?

 

[Aladin Akyurek]

whats in p2?

Implement the formula in P2 or in F9...


If in P2...

=IFERROR(INDEX($E$9:$E$31,INDEX(MODE.MULT(IF(ISTEXT($E$9:$E$31),IF(ISNA(MATCH($E$9:$E$31,{"","unknown"},0)),MATCH($E$9:$E$31,$E$9:$E$31,0)))),ROWS($P$2:$P2))),"")

If in F9...

=IFERROR(INDEX($E$9:$E$31,INDEX(MODE.MULT(IF(ISTEXT($E$9:$E$31),IF(ISNA(MATCH($E$9:$E$31,{"","unknown"},0)),MATCH($E$9:$E$31,$E$9:$E$31,0)))),ROWS($F$9:$F2))),"")


If any other cell of your liking...

=IFERROR(INDEX($E$9:$E$31,INDEX(MODE.MULT(IF(ISTEXT($E$9:$E$31),IF(ISNA(MATCH($E$9:$E$31,{"","unknown"},0)),MATCH($E$9:$E$31,$E$9:$E$31,0)))),ROWS($1:1))),"")

 

[Patcheen]

thank you for all your help

this is the one im using it works a treat thank you

=IFERROR(INDEX($E$9:$E$31,INDEX(MODE.MULT(IF(ISTEXT($E$9:$E$31),IF(ISNA(MATCH($E$9:$E$31,{"","unknown"},0)),MATCH($E$9:$E$31,$E$9:$E$31,0)))),ROWS($1:1))),"")

 

[Aladin Akyurek]

You are welcome.