Using an Array Formula to Find and Count the Maximum Text Occurrences in a Range

A week ago Tarun asked a question on the Chandoo.org Forums.

“I have got multiple names in each row and would like to have what name is repeated maximum number of times and how many times?

Eg. Ram, Amita, Obama, Ram, Willi, Ram, Amita, Chandoo, Ram, Willi

Ans: Ram (4 times)”

(The list and answers are edited)

Chandoo responded with a neat Array Formula:

=INDEX(B2:K2,MATCH(MAX(COUNTIF(B2:K2,B2:K2)), COUNTIF(B2:K2,B2:K2),0))  &

” (“&MAX(COUNTIF(B2:K2,B2:K2))&” times)”

Lets take a look inside this and see how it works

THE EXAMINATION

The formula has two parts separated by a &

=INDEX(B2:K2,MATCH(MAX(COUNTIF(B2:K2,B2:K2)), COUNTIF(B2:K2,B2:K2),0))

and

&

and

” (“&MAX(COUNTIF(B2:K2,B2:K2))&” times)”

Each part is separate and can be used independently, the & character simply joins the two parts together to make a single string which answers Tarun’s question, Ram (4 times).

Now, lets look at each part.

You can follow along with this forensic examination by downloading the Sample Data File.

=INDEX(B2:K2,MATCH(MAX(COUNTIF(B2:K2,B2:K2)), COUNTIF(B2:K2,B2:K2),0))

This is a single Index Function with 2 components, being:

a Range B2:K2 and

a Count  MATCH(MAX(COUNTIF(B2:K2,B2:K2)), COUNTIF(B2:K2,B2:K2),0)

Typically an Index Function uses 3 components

=Index(Array, Row Number,[Column Number])

In this example the Range is a single Row, B2:K2

And so using the Counter in the Row spot has the effect of counting down the first Column and then continuing at the top of the second Column etc

So the formula used:

=INDEX(B2:K2,MATCH(MAX(COUNTIF(B2:K2,B2:K2)), COUNTIF(B2:K2,B2:K2),0))

Is equivalent to:

=INDEX(B2:K2,1,MATCH(MAX(COUNTIF(B2:K2,B2:K2)), COUNTIF(B2:K2,B2:K2),0))

Now lets jump ahead to the COUNTIF(B2:K2,B2:K2) bit

If you copy =COUNTIF(B2:K2,B2:K2) to a cell, Press F2 and then evaluate the Formula using F9

You will see that it returns an array. The array is highlighted by the squiggly brackets {  } ‘s

={4,2,1,4,2,4,2,1,4,2}

This is the heart of the solution.

What this is showing us is that for each position in the range B2:K2, the count of how many times that cells value occurs in the range B2:K2

So the formula

=INDEX(B2:K2,MATCH(MAX(COUNTIF(B2:K2,B2:K2)), COUNTIF(B2:K2,B2:K2),0))

Is equivalent to

=INDEX(B2:K2,MATCH(MAX({4,2,1,4,2,4,2,1,4,2}), {4,2,1,4,2,4,2,1,4,2},0))

Looking at the MAX({4,2,1,4,2,4,2,1,4,2}) part, this simplifies to 4, the Maximum value of the array (Remember this line, we’ll come back to it later).

So our simplified formula is now: =INDEX(B2:K2,MATCH(4, {4,2,1,4,2,4,2,1,4,2},0))

Now looking at the MATCH(4, {4,2,1,4,2,4,2,1,4,2},0) part of the equation

You can see that Match is looking for the value 4, in the array {4,2,1,4,2,4,2,1,4,2}, which is the First value , Position 1, the 0 requesting that an exact match is found.

So that MATCH(4, {4,2,1,4,2,4,2,1,4,2},0) is equivalent to 1

So our equation =INDEX(B2:K2,MATCH(4, {4,2,1,4,2,4,2,1,4,2},0))

Is now simplified even more to =INDEX(B2:K2, 1)

Index will then look in B2:K2 and will return the first cell or “Ram” in this example.

& “(” & MAX(COUNTIF(B2:K2,B2:K2)) & ” times)”

The second part of the equation is responsible for counting the number of Times Ram occurs and displaying it with some text.

& “(” & MAX(COUNTIF(B2:K2,B2:K2)) & ” times)”

The parts displayed in Red above add the text ( and times) to the Count

Remember the section MAX(COUNTIF(B2:K2,B2:K2)) which was explained above and evaluates to 4 in this case

So the & “(” & MAX(COUNTIF(B2:K2,B2:K2)) & ” times)”

Part evaluates to: ( 4 times)

With the initial & adding it to the text of the first part Ram for the final result – Ram ( 4 times)

LEARN MORE ABOUT ARRAY FORMULAS

You can learn more about Array Formulas at the following links:

http://www.cpearson.com/excel/ArrayFormulas.aspx

http://www.databison.com/index.php/excel-array-formulas-excel-array-formula-syntax-array-constants/

http://office.microsoft.com/en-us/excel-help/introducing-array-formulas-in-excel-HA001087290.aspx

Chandoo.org has several articles on Array Formulas

http://chandoo.org/wp/tag/array-formulas/

FORENSIC FORMULAS

Would you like to see more “Forensic” examination of complex formulas ?

Let us know in the comments below and it may become a regular section at Chandoo.org.