Return most common text from column cells with formula while ignoring blanks

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.

To all replies so far,

Thank you so much for the replies but all except the pivot table are still returning blank. The pivot table is a good way if the text stays the same but it needs to be dynamic as it may/will change frequently based on evolving data.

I have uploaded a sample of what I need with what I have in my original post.

Thank you for your answers so quickly.

Rosco01995

Canapone, Thank you that worked brilliantly and gives me the top one (most frequent) every time. Many, many Thanks to all.

Hi,

Another option, Confirm with ctrl+Shift+Enter.

=INDEX($W$8:$W$49,MODE(IF($W$8:$W$49<>0,MATCH($W$8:$W$49,$W$8:$W$49,0))))

Regards,