Most occuring text value with criteria. MODE doesn't work

user_10749681 0
Viewed 24

I'm struggling with function to output most occuring value in a range that ignores two values (` and -)

=INDEX(Download_1[01.12.2020];MATCH(MAX(COUNTIF(Download_1[01.12.2020];Download_1[01.12.2020]));COUNTIF(Download_1[01.12.2020];Download_1[01.12.2020]);0))

Right now it outputs "`", which is true but I want to skip this value and another "-" to provide 3rd most occuring one.

I've tried different formulas with MODE but they don't seem to work on text values in a range. Can you help me modify the formula or provide a new one?

excel-formula excel arrays
1 Answers

For me the following works including MODE():

enter image description here

Formula in B1 using a nested IF():

=INDEX(A1:A13,MODE(IF(A1:A15<>"`",IF(A1:A15<>"-",MATCH(A1:A15,A1:A15,0)))))

Or using a boolean structure:

=INDEX(A1:A15,MODE(IF((A1:A15<>"`")*(A1:A15<>"-"),MATCH(A1:A15,A1:A15,0))))

Note: This is an array formula which need to be entered through CtrlShiftEnter if you don't have Microsoft365.

user_97581940
Related