Formula Forensics 011. Lykes Formula

Formula Forensics 011. Lykes Formula:
Lykes, a Chandoo.org follower, submitted this problem for analysis by Formula Forensics.
“I have a list of words and I want to find out how many times each record in the list contains an entry from another list of single characters. I use the following equation, but how does it work? ”
=SUMPRODUCT(COUNTIF(B3,”*”&$E$3:$E$6&”*”))
So today we will pull this apart to see what’s inside.

Lyke’s Formula

As usual we will work through this formula using a sample file for you to follow along. You can download it here. Download Here.
Lykes has a Sumproduct based formula
=SUMPRODUCT(COUNTIF(B3,”*”&$E$3:$E$6&”*”))
Lets look at cell C3 as our example.


In C3 we see the formula: =SUMPRODUCT(COUNTIF(B3,”*”&$E$3:$E$6&”*”))
Which consists of a Sumproduct and a Countif.
We know from Formula Forensics 007 that Sumproduct, Sums the Product of the Arrays, and that when there is only 1 array it simply sums the array elements.
In this case Sumproduct only has a single array as an element
=SUMPRODUCT(COUNTIF(B3,”*”&$E$3:$E$6&”*”))
and so the COUNTIF(B3,”*”&$E$3:$E$6&”*”) component must return an Array of elements for the Sumproduct to sum.
If we now look at the COUNTIF(B3,”*”&$E$3:$E$6&”*”) component.
The Excel Countif() function has the format COUNTIF(Range, Criteria).
Countif() will look through a Range and Count the occurrences of the Criteria.
In our case:
The Range is: B3, The Source Cell
The Criteria is: “*”&$E$3:$E$6&”*”
The Criteria is a Text Concatenation or Joining of a * and the cells in the range $E$3:$E$6 and a Final *. It is saying I want the value/s from E3:E6 with any value in front or after the value from E3:E6.
This is where the magic of Sumproduct kicks in.
Sumproduct forces this to be evaluated as an Array and so each cell in the Criteria has a * added to each end and is then compared against the Range in the Countif.
So in other words, Countif is looking for any occurrence of the characters in the Criteria Range $E$3:$E$6 with any characters preceding or trailing them, in the Cell B3.
We can see this if in a blank cell say E12: we enter the following:
=COUNTIF(B3,”*”&$E$3:$E$6&”*”) press F9 not Enter.
Excel will respond with ={1;1;1;0}
This is showing us that the 1^st, 2^nd and 3^rd elements in $E$3:$E$6, are found in B3, which we can see below:


Sumproduct now only has to add up the Array
=Sumproduct({1;1;1;0})
Which it does returning 3.
Examine the other cells in the Text range and see what is happening.

Your Challenge

Try changing the values in the Text or Word List Column and see what effects it has on the answers.

Download

You can download a copy of the above file and follow along, Download Here.

Formula Forensics “The Series”

You can learn more about how to pull Excel Formulas apart in the following posts
Formula Forensic Series:

We Need Your Help

I have received a few more ideas since last week and these will feature in coming weeks.
I do need more ideas though and so I need your help.
If you have a neat formula that you would like to share and explain, try putting pen to paper and draft up a Post like above or;
If you have a formula that you would like explained but don’t want to write a post also send it to Chandoo or Hui.