Gandalf's notebook: Using an Array Formula to Find and Count the Maximum Text Occurrences in a Range

From Chandoo.org

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

” (“&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.