I know there are already formulas floating on the web to find the most frequently occurring text string, we can say Mode, in a list. What about finding multiple Mode of text values in Google Sheets?
Sometimes there may be two or more most frequently occurring text in a list. I have a Google Sheets formula that you can use to find the MODE as well as MODE.MULT of text strings.
As you may know, we can’t use the MODE or MODE.SNGL (both are the same in use) function to find the Mode of non-numerical values. Similarly, we can’t use the MODE.MULT to return multiple Modes of non-numerical values. All these three are statistical functions.
Then how to find the Mode of text values in Google Sheets?
As per the below list in column A, the Mode of text is “Italy” as it repeats 4 times that is more than any other text values in that list.
But in the list in column D, the string “Italy” and “France” repeated four times each. So both these text values are the most frequently occurring text values (multiple modes) in this list.
Formula to Find the Mode of Non-Numeric Values in Google Sheets
My formula will work in both the cases, I mean to return single as well as multiple modes, that without any modification.
Formula to Find the Mode/Modes of Text Strings in Google Sheets:
=unique(
filter(
A2:A,
regexmatch(
match(A2:A,A2:A,0)&"",
"^"&textjoin("$|^",true,MODE.MULT(iferror(match(A2:A,A2:A,0))))&"$"
)
)
)This formula is coded to work in the list in columns A (range A2:A). If your list to find the mode is in any other column, please modify the cell references in the formula accordingly.
How the Formula Finds the Most Frequently Occurring Strings in Google Sheets?
I know just providing a complex looking formula is not enough for you. Some of you may want to know how the formula finds the most occurring text string from a list.
I have used several functions in my formula. I’ll try to explain the role of each function used in the simplest way as I can.
Match to Return the Relative Position of All the Text Strings in the List
In cell B2, enter this Match formula.
Formula 1:
=ArrayFormula(match(A2:A,A2:A,0))It will return the relative position of each item in a range, here A2:A, based on the unique values in that range.
For example “Italy”, “France”, and “Ireland” are the three unique values in the list in A2:A.
The relative position of the text “Italy” is 1 as it is in the first row in the range A2:A. The relative position of the text “France” is 5 as it’s in the fifth row in the range A2:A.
Take a look at the relative position of the string “Ireland” and you can see that it’s 8.
The Role of MODE.MULT Function in Finding the Most Frequently Occurring Text Values
After entering the Match formula above, we have got a few numbers in column B, right?
What we want is to find the Mode of those numbers. For that, you can use MODE, MODE.SNGL or MODE.MULT function.
I suggest you go ahead with the MODE.MULT function as it can return multiple mode values.
Use the above same Match formula within MODE.MULT, but include the IFERROR function to remove N/A error values.
Formula 2:
=ArrayFormula(MODE.MULT(iferror(match(A2:A,A2:A,0))))
The formula returns 1 which is the most frequently occurring number in column B. We can now filter the rows containing the value 1 using a Filter formula.
Filter to Filter a List Based on Most Frequently Occurring Match Value
Filter Syntax:
FILTER(range, condition1)The Generic Formula to filter the most frequently occurring text string will be as below.
FILTER(A2:A, Formula 1=Formula 2)So the formula will be;
=filter(
A2:A,
match(A2:A,A2:A,0)=MODE.MULT(iferror(match(A2:A,A2:A,0)))
)I have removed the ArrayFormula from the Formula 1 and Formula 2 as it doesn’t require in Filter.
Wrap the above Filter with the Unique function to return the mode of text value from column A.
=unique(
filter(
A2:A,
match(A2:A,A2:A,0)=MODE.MULT(iferror(match(A2:A,A2:A,0)))
)
)If you want to return only one most frequently occurring text, then the above formula is OK for you. In the above list in column A, there is only one most frequently occurring text string.
As I have promised in the beginning, I am going to provide you a more flexible formula that works well in single as well as multiple modes.
Just skip the above step “Filter to Filter…” and here is the continuation from the earlier step “The Role of MODE.MULT Function…”.
TextJoin to Combine Numbers to Use as a Regex Expression
Formula 3:
Modify the Formula 2 as below. I have here included the TextJoin function.
=ArrayFormula(
"^"&textjoin("$|^",true,MODE.MULT(iferror(match(A2:A,A2:A,0))))&"$"
)It will place a | symbol between the model values returned by Formula 2 as a separator. In addition to this, a ^ sign will be inserted before each value and a $ sign after each value.
You are using this formula in the range A2:A. So you won’t see any difference as there is only one model value in that range.
To test, use the range D2:D (see the first screenshot above) instead of A2:A to see the below output.
^1$|^5$We can use this value in the RegexMatch formula for this or that match. That means, match either 1 or 5, or both.
You May Like: Regexmatch in Filter Criteria in Google Sheets.
Regexmatch the Model Values in Match Returned Relative Position Numbers
Here is the final step. We are going to use Filter as per the generic formula below.
FILTER(A2:A, regexmatch(Formula 1&" ",Formula 3)Note: In this, I have added a " " (space) to Formula 1. It is because the Formula 1 output is relative positions which are numeric values. Adding a white space we can format the numbers to text. I have done that as the Regexmatch requires text input.
My final formula is based on the above generic formula. See that final formula once again.
=unique(
filter(
A2:A,
regexmatch(
match(A2:A,A2:A,0)&"",
"^"&textjoin("$|^",true,MODE.MULT(iferror(match(A2:A,A2:A,0))))&"$"
)
)
)Formula Logic:
The above formula filters the range A2:A if the relative position returned by the Formula 1 is equal to the multiple modes returned by the Formula 3. Then unique the output to remove duplicates.
You can use this method to find the Mode of text Values in Google Sheets.
Hello Prashanth, thank you for this info and for helping everyone learn.
I am trying to transpose the formula to return the most common value within rows, specifically I8:AX8, in my sheet.
It does return the most common value, but it repeats the value for the number of matched cells instead of just once.
=unique(filter(I8:AX8,regexmatch(
match(I8:AX8,I8:AX8,0)&"",
"^"&textjoin("$|^",true,MODE(iferror(match(I8:AX8,I8:AX8,0))))&"$")))
Hi, Charles Jackson,
When using UNIQUE horizontally, do make appropriate changes to it.
Syntax:-
UNIQUE(range, [by_column], [exactly_once])We should use the optional argument
by_column. So in your formula, before the last closing bracket, put,1.Nice work. Thank you.
Hi Prashanth,
This is super helpful, thank you so much
Is that possible to filter the top 3 most frequent strings?
Hi, Kelvin,
See if this new tutorial helps?
How to Filter the Top 3 Most Frequent Strings in Google Sheets
You can find a sample sheet linked at the end of the tutorial. Any issue, please let me know.
Hi Prashanth! Super helpful thankyou. Your formula worked perfectly but I need to take it a level deeper and am having trouble.
Each of the Text values in the column I have is associated with a date from another column.
I need a formula similar to your own that tells me the MOST often text between two dates.
Thanks in Advance!
Hi, Blake,
Just replace all the references $B$1:$B in the formula with the below Filter formula.
filter(B2:B,A2:A>=date(2020,6,18),A2:A<=date(2020,7,23),B2:B<>"")This filters column B (strings) if the dates in column A are between two dates (18/06/2020 and 23/07/2020).
Thanks for the fast response!
I modified it to get the MODE txt value for June 19th… tested it out and that worked thank you!
But when I drag the formula down of course dates don’t update with that format. You will see what I tried to do in Cell D4 and got an error.
Do you know how to get the formula to dynamically update for each given day based on the date value in column C?
Hi, Blake,
The problem is the timestamp. Use the below formula.
=filter($B$2:$B,int($A$2:$A)>=C2,int($A$2:$A)<=C3,$B$2:$B<>"")Thanks for all your help so far Prashanth. I feel like we are soo close.
But that formula returns the inverse of the MODE txt value when I tried it. If I did it right. And then it also messes up the formula in subsequent cells as I drag it down cause it returns more than one text value. I used MODE.SNGL and it seemed to kind of fix it but I’m still getting the wrong MODE txt values.
Hi, Blake,
I’ve requested edit access to your sheet. Thanks.
Hi, Blake,
Thanks for giving edit access to your sheet.
You were asking to find the mode of text values in column B (B2:B) based on the dates in column A is between the dates in C2 and C3.
Here is the formula in D2.
=iferror(unique(filter($B$2:$B,match($B$2:$B,$B$2:$B,0)=MODE(iferror(filter(match($B$2:$B,$B$2:$B,0),int($A$2:$A)>=C2,
int($A$2:$A)<=C3))))))
Drag this formula down to change the date between the dates given in C2 and C3 to C3 and C4, and so on.
The change required is in the MODE formula. You can see a Filter used to filter out the match (relative position) outside the date range in C2 and C3.
Hope this helps?
I am trying to use this function for a list of responses. The possible responses are: Strongly Agree, Agree, Mixed, Disagree, Strongly Disagree.
For some reason, with some data sets but not others, this function first returns the most common response, then it returns one or two other values which seem to be the least common, and second least common.
I can’t figure out the logic beyond the first value it returns. Can you explain why I might be seeing this?
Hi, Jonny,
Thanks for pointing out the error!
I have gone through each and every step once again and could find the reason.
I must have used an exact match in Regexmatch. For example, instead of
1|5(please refer the tutorial), I must have used it as^1$|^5$. Earlier the formula was treating 1 and 11 (partial match) as the same value.Please use the below formula.
=unique(filter(A2:A,regexmatch(match(A2:A,A2:A,0)&"","^"&textjoin("$|^",true,MODE.MULT(iferror(match(A2:A,A2:A,0))))&"$")))
Thanks again.
Updated the formulas within the post too…
Thanks a lot, this really helped me!!!!
Just a hint: I don’t know if it’s the only mine, but to make it works on spreadsheets I had to change all the commas for semicolons 🙂
But it worked like a charm!
Hi, luisa,
Thanks for your feedback!
The comma to semicolon change is due to Google Sheets’ regional settings.
Best,