Skip to main content

Kutools for Office ā€” One Suite. Five Tools. Get More Done.

How to find the most frequent text with criteria in Excel?

Author Xiaoyang Last modified

This article explains how to find the most frequent text in a range of cells in Excel based on specific criteria. Additionally, I'll introduce formulas for extracting the most common text in a column.

Find the most frequent text Find the most frequent text based on criteria
A screenshot showing the Excel interface with the formula to find most frequent text in a range of cells A screenshot showing the Excel interface with the formula to find most frequent text in a range based on criteria

Find the Most Frequent Occurring Text in a Column with an Array Formula

If you just want to find and extract the most frequent text from a list of cells, apply the formula below:

The generic formula syntax is:

=INDEX(range, MODE(MATCH(range, range, 0)))
  • range: is the list of cells where you want to find the most frequent text.

1. Enter or copy this formula into a blank cell where you want the result to appear:

=INDEX(A2:A15,MODE(MATCH(A2:A15,A2:A15,0)))
  • Tips: In this formula, A2:A15: is the data list from which you want to find the text that occurs most frequently.

2. Then, press Ctrl + Shift + Enter keys simultaneously, and you'll get the correct result as shown in the screenshot below:

A screenshot showing the result of the most frequent text calculation in Excel

Find the Most Frequent Occurring Text Based on Criteria with Array Formulas

In some cases, you might need to find the most frequent text based on a specific condition. For example, you may want to find the most frequent name for Project A, as shown in the screenshot below:

A screenshot showing a sample Excel sheet with project names and criteria for finding frequent text

The generic formula syntax is:

=INDEX(range1, MODE(IF(range2=criteria, MATCH(range1, range1, 0))))
  • range1: is the range of cells where you want to find the most frequent text.
  • range2=criteria: is the range of cells containing the specific criteria that you want to find the text based on.

1. Please enter or copy the following formula into a blank cell:

=INDEX($B$2:$B$15, MODE(IF($A$2:$A$15=D2, MATCH($B$2:$B$15, $B$2:$B$15, 0))))
  • Tips: In this formula:
  • B2:B15: is the data list from which you want to find the text that occurs most frequently.
  • A2:A15=D2: is the range of cells that contains the specific criteria for which you want to find the most frequent text.

2. After entering the formula, press Ctrl + Shift + Enter keys together. The most frequent name for Project A will be extracted, as shown in the screenshot:

A screenshot showing the result of the most frequent text extraction based on project criteria

Find the Most Frequent Occurring Text Between Two Given Dates with Array Formula

This section explains how to find the most frequent text between two given dates. For example, to find the most frequent name between 6/28/2019 and 7/5/2019, use the following array formula:

1. Please enter the following formula into a blank cell:

=INDEX($B$2:$B$15, MATCH(MODE.SNGL(IF(($A$2:$A$15<=$E$2)*($A$2:$A$15>=$D$2), COUNTIF($B$2:$B$15, "<"&$B$2:$B$15), "")), COUNTIF($B$2:$B$15, "<"&$B$2:$B$15),0))
  • Tips: In this formula:
  • B2:B15: is the data list from which you want to find the text that occurs most frequently.
  • A2:A15<=E2: is the range of dates that are less than or equal to a specific date that you want to find the name based on.
  • A2:A15>=D2: is the range of dates that are greater than or equal to a specific date that you want to find the name based on.

2. Press Ctrl + Shift + Enter simultaneously to get the result.

A screenshot showing the result of the most frequent text extraction between two dates in Excel

More relative most frequent text articles:

  • Find The Second Most Common/Frequent Number Or Text In Excel
  • We can apply the MODE function to find out the most frequent number from a range at ease in Excel. However, how about finding out the second most frequent number from a column? And what if finding out the second most common text value from a column? Here we get some workarounds for you.
  • Find Most Common Value(Number Or Text String) From A List In Excel
  • Supposing you have a list of names which contain some duplicates, and now, you want to extract the value that appears the most frequently. The direct way is to count the data one by one from the list to get the result, but if there are thousands of names in the column, this way will be troublesome and time-consuming. The following tutorial will introduce you some tricks to solve this task quickly and conveniently.
  • Sort Data By The Most Frequent Value In Excel
  • Supposing you have a long list of data in your worksheet, and now you would like to sort this list by the frequency of which each word occurs. That is to say, the most common value (for example, occurs four times in the column) is listed first, and then be followed by the words which occur three times, twice and once as following screenshots shown. How could you solve this task in Excel?

Best Office Productivity Tools

šŸ¤– Kutools AI Aide: Revolutionize data analysis based on: Intelligent Execution   |  Generate Code  |  Create Custom Formulas  |  Analyze Data and Generate Charts  |  Invoke Kutools Functionsā€¦
Popular Features: Find, Highlight or Identify Duplicates   |  Delete Blank Rows   |  Combine Columns or Cells without Losing Data   |   Round without Formula ...
Super Lookup: Multiple Criteria VLookup  |   Multiple Value VLookup  |   VLookup Across Multiple Sheets   |   Fuzzy Lookup ....
Advanced Drop-down List: Quickly Create Drop Down List   |  Dependent Drop Down List   |  Multi-select Drop Down List ....
Column Manager: Add a Specific Number of Columns  |  Move Columns  |  Toggle Visibility Status of Hidden Columns  |  Compare Ranges & Columns ...
Featured Features: Grid Focus   |  Design View   |   Big Formula Bar   |  Workbook & Sheet Manager   |  Resource Library (Auto Text)   |  Date Picker   |  Combine Worksheets   |  Encrypt/Decrypt Cells   |  Send Emails by List   |  Super Filter   |   Special Filter (filter bold/italic/strikethrough...) ...
Top 15 Toolsets:  12 Text Tools (Add Text, Remove Characters, ...)   |   50+ Chart Types (Gantt Chart, ...)   |   40+ Practical Formulas (Calculate age based on birthday, ...)   |   19 Insertion Tools (Insert QR Code, Insert Picture from Path, ...)   |   12 Conversion Tools (Numbers to Words, Currency Conversion, ...)   |   7 Merge & Split Tools (Advanced Combine Rows, Split Cells, ...)   |   ... and more
Use Kutools in your preferred language ā€“ supports English, Spanish, German, French, Chinese, and 40+ others!

Supercharge Your Excel Skills with Kutools for Excel, and Experience Efficiency Like Never Before. Kutools for Excel Offers Over 300 Advanced Features to Boost Productivity and Save Time.  Click Here to Get The Feature You Need The Most...

Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier

  • Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
  • Open and create multiple documents in new tabs of the same window, rather than in new windows.
  • Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!

All Kutools add-ins. One installer

Kutools for Office suite bundles add-ins for Excel, Word, Outlook & PowerPoint plus Office Tab Pro, which is ideal for teams working across Office apps.

Excel Word Outlook Tabs PowerPoint
  • All-in-one suite ā€” Excel, Word, Outlook & PowerPoint add-ins + Office Tab Pro
  • One installer, one license ā€” set up in minutes (MSI-ready)
  • Works better together ā€” streamlined productivity across Office apps
  • 30-day full-featured trial ā€” no registration, no credit card
  • Best value ā€” save vs buying individual add-in