Formula help - excluding a specific text value from a mode range of text values

[k-dre]

Hi all

Hope you can help!

I've made a 'Summary' worksheet which I need to tell me the most frequent text value from a row of data on another tab ('Company Ltd'), but to exclude the value '?'.

The below formula is what I have, which does work fine to give me the mode, but as the row contains dropdown lists in each cell (made in 'data validation') which have a default '?' value, I need to exclude these to stop the mode being '?', and I can't work out how to add this exclusion to the below formula.


=INDEX('Company Ltd'!$E$51:$CZ$51,MODE(MATCH('Company Ltd'!$E$51:$CZ$51,'Company Ltd'!$E$51:$CZ$51,0)))

I have several worksheets within the same workbook, all for different companies but with exactly the same setup and fields, so once I can get the below working correctly I can then replicate the formula to pull through the mode for each company to the Summary tab.

Sorry I'm definitely not an Excel whizz!


Many thanks for reading!

[jason.b75]

Not tested, but should work

=INDEX('Company Ltd'!$E$51:$CZ$51,MODE(IFERROR(MATCH(IF('Company Ltd'!$E$51:$CZ$51<>"?",'Company Ltd'!$E$51:$CZ$51),'Company Ltd'!$E$51:$CZ$51,0),"")))

Array confirmed with Shift Ctrl Enter