Tip: Other languages are Google-Translated. You can visit the English version of this link.
Log in
x
or
x
x
Register
x

or

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

This article will talk about how to find the most occurred text based on the specific criteria from a range of cells in Excel. At the same time, I will also introduce the formula for extracting the most frequently occurring text in a column.

Find the most frequent text Find the most frequent text based on criteria

Find the most frequent occurring text in a column with array formula

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

The generic formula syntax is:

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

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

=INDEX(A2:A15,MODE(MATCH(A2:A15,A2:A15,0)))
  • Tips: In this formula:
  • A2:A15: is the data list that you want to find the greatest number of times a text occurs.

2. And then, press Ctrl + Shift + Enter keys simultaneously, and you will get the correct result as below screenshot shown:


Find the most frequent occurring text based on criteria with array formulas

Sometimes, you may need to find the most frequent occurring text based on a specific condition, for example, you want to find the most occurred name which Project is Project A as below screenshot shown:

The generic formula syntax is:

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

1. Please enter or copy the below 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 that you want to find the greatest number of times a name occurs.
  • A2:A15=D2: is the range of cells contain the specific criteria that you want to find text based on.

2. And then, press Ctrl + Shift + Enter keys simultaneously, the most frequent occurring name of Project A has been extracted, see screenshot:


Find the most frequent occurring text between two given dates with array formula

This section is going to talk about how to find the most common text which is between two given dates. For instance, to find the most frequent name based on the dates which is between 6/28/2019 and 7/5/2019, the following array formula may help you:

1. Please apply the below 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 that you want to find the greatest number of times a name occurs.
  • A2:A15<=E2: is the range of dates that 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 greater than or equal to a specific date that you want to find the name based on.

2. And then, press Ctrl + Shift + Enter keys together, the most frequent occurring name between the specific two dates has been extracted, see screenshot:


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?


  • Super Formula Bar (easily edit multiple lines of text and formula); Reading Layout (easily read and edit large numbers of cells); Paste to Filtered Range...
  • Merge Cells/Rows/Columns and Keeping Data; Split Cells Content; Combine Duplicate Rows and Sum/Average... Prevent Duplicate Cells; Compare Ranges...
  • Select Duplicate or Unique Rows; Select Blank Rows (all cells are empty); Super Find and Fuzzy Find in Many Workbooks; Random Select...
  • Exact Copy Multiple Cells without changing formula reference; Auto Create References to Multiple Sheets; Insert Bullets, Check Boxes and more...
  • Favorite and Quickly Insert Formulas, Ranges, Charts and Pictures; Encrypt Cells with password; Create Mailing List and send emails...
  • Extract Text, Add Text, Remove by Position, Remove Space; Create and Print Paging Subtotals; Convert Between Cells Content and Comments...
  • Super Filter (save and apply filter schemes to other sheets); Advanced Sort by month/week/day, frequency and more; Special Filter by bold, italic...
  • Combine Workbooks and WorkSheets; Merge Tables based on key columns; Split Data into Multiple Sheets; Batch Convert xls, xlsx and PDF...
  • Pivot Table Grouping by week number, day of week and more... Show Unlocked, Locked Cells by different colors; Highlight Cells That Have Formula/Name...
kte tab 201905
  • 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!
officetab bottom
Say something here...
symbols left.
You are guest ( Sign Up? )
or post as a guest, but your post won't be published automatically.
Loading comment... The comment will be refreshed after 00:00.
  • To post as a guest, your comment is unpublished.
    Caleb1T · 6 days ago
    To extract the word or text value that occurs most frequently in a range, you can use a formula based on several functions INDEX, MATCH, and MODE https://www.mcdvoice.onl/