Identifying Most Common Occurrences (Text) with Criteria

Hello,

I am trying to generate a list of the five most common text occurrences using criteria to filter the results.

So far, I am able to create an INDEX MATCH function to identify which text occur most frequently in a range using criteria, but if I copy this formula down, I duplicate the result. Instead, I am using a different formula for results two - four, but do not know how to modify it so I can include my criteria. Can someone please explain how I can implement the criteria into the formula for the remaining four results?

Please find a copy of my workbook and let me know if you need any clarification.

Kind regards,
preppy_prince

preppy_prince_Example_30052023.xlsx

*UPDATE*

I have amended my workbook as I realised I need to return results based on more than one criteria, with each criterion containing multiple 'correct' values which my formula is unable to account for.

The criteria I am now wanting the INDEX MATCH function to refer to are:

• D2:D31 | "Full-time" or "Part-time"
• E2:E31 | "Trainer" or "Trainee"

This should return the result 'Emma', (as her name appears the most out of all texts in B2:B31 which also satisfy the two criteria) but instead I am receiving #N/A.

preppy_prince_Example_30052023_V2.xlsx

*UPDATE*

New workbook where I am trying to return results using multiple 'or' criteria:

preppy_prince_Example_30052023_V3.xlsx

with Power Query

I'm not sure your Mac contains PQ but you can try

Try this:

Formula:

Please Login or Register  to view this content.

there were NO other matches, apart from Emma, so I introduced a few.

Also, please confirm the Excel PRODUCT (e.g. O365 for Mac) and VERSION that you are currently using

Please Login or Register  to view this content.

committed with Ctrl+Shift+Enter

windknife, that is incorrect. It is ONLY matching the first criterion (123 and Cancelled). You need to use MATCH.

Thus:

=IFERROR(INDEX($B$2:$B$31,MODE(IF((COUNTIF($F$1:$F1,$B$2:$B$31)=0)*(ISNUMBER(MATCH($C$2:$C$31,{123,126,127},0)))*(ISNUMBER(MATCH($D$2:$D$31,{"Cancelled","Rescheduled","TBD"},0))),MATCH($B$2:$B$31,$B$2:$B$31,0)))),"")

If you also want to return Diane and Sophie, who meet the criteria, BUT only occur once, you need to add the +{0,0}

=IFERROR(INDEX($B$2:$B$31,MODE(IF((COUNTIF($F$1:$F1,$B$2:$B$31)=0)*(ISNUMBER(MATCH($C$2:$C$31,{123,126,127},0)))*(ISNUMBER(MATCH($D$2:$D$31,{"Cancelled","Rescheduled","TBD"},0))),MATCH($B$2:$B$31,$B$2:$B$31,0)+{0,0}))),"")

You're welcome.

---

It would be very nice if you were to just click the Add Reputation button at the foot of any of the posts of members who helped you reach a solution.

Finally, if that takes care of your original question, please click on "Thread Tools" from the menu link (just above the first post in the thread) and mark this thread as SOLVED.