Hi,

Have used this forum many times to work through excel problems and have marveled at some of the responses. You guys rock! However, I have one problem now that I can't find a solution for and was hoping you could help me.

I have a spreadsheet which in cells $W8:W49 have formula in it to return a text string if corresponding cells from lower order columns have certain text in them. an example of the formula is as follows:

=IF((E8="O")*AND(F8="O"),'G:\[Evacuation Data.xlsx]Building & Codes'!P8,IF((E8="O")*AND(F8="P"),'G:\[Evacuation Data.xlsx]Building & Codes'!M8,))

If the corresponding cell is blank, no text is displayed but the formula is in there still to check.

This works fine but, when I want cell W53 to return the most common text in $W$8:$W$49 it doesn't discriminate between so called blank cells (due to formula in cells I think) and those with the text in them.
I have been using the following array formula {}:

=INDEX($W$8:$W$49,MATCH(MAX(COUNTIF($W$8:$W$49,$W$8:$W$49),),COUNTIF($W$8:$W$49,$W$8:$W$49),0))

This seems to work but if there are more blanks than text filled cells it returns blank.

I need it to give me the most common of text if text appears. I have tried variations using "?*" but cant find one that works.

Your help would be much appreciated if you can solve this for me. I initially wanted the top 3 text strings (in separate cells) but narrowed to the top one because it was becoming to complex and time consuming. If it can do the top 3 (in separate cells) I would be jumping for joy.

Thank you in advance and i look forward to seeing you solutions.

Rosco01995.