Menu
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 1st, 2nd and 3rd 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
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.
Hello Awesome...
My name is Chandoo. Thanks for dropping by. My mission is to make you awesome in Excel & your work. I live in Wellington, New Zealand. When I am not F9ing my formulas, I cycle, cook or play lego with my kids. Know more about me.
I hope you enjoyed this article. Visit Excel for Beginner or Advanced Excel pages to learn more or join my online video class to master Excel.
Thank you and see you around.
Related articles:
|
Leave a Reply
| « Using external software packages to manage your spreadsheet risk [Part 4 of 4] | How would you customize Excel after installing? [poll] » |
At Chandoo.org, I have one goal, "to make you awesome in Excel & Power BI". I started this website in 2007 and today it has 1,000+ articles and tutorials on data analysis, visualization, reporting and automation using Excel and Power BI. 
14 Responses to “Formula Forensics 011. Lykes Formula”
Interesting....
How would you do the reverse? Count how many times the text contains the word list? So
Word List Freg
a 12
b 6
etc
I couldn't downlload the sample file so I recreated it and have some questions about the formula.
They way I understand it, the formula counts the number of times any of the 4 single letters in E3:E6 appear in the target cell - one of B3 through B9.
But starting in B6, you get multiple instances of the same letter - mostly f. The formula counts it as one appearance. I would have guessed that the correct answer for B6 would be 2, B7 would be 2, B8 would be 4 and B9 would be 2.
Perhaps I'm misreading the problem we are trying to solve.
@Sachin
The question maybe should have been put as "How many of the small list appear in the text"
@All
The download and other links have been fixed
Hi
In the forum Hui said that the all the links have been fixed. No they have not. They are still not working, sir
@Ahmed
They are all working
You may have to Clear your web browsers Cache
Thanks Hui for another great article. I understand (and often use) how press F9 will display the array results, but is there a way to display the F9 results in a cell?
For example, when you press F9 (in the example above), you see {1;1;1;0}. Is there a formula or function that would display this whole array in a cell? Entering the formula as an array (Ctrl-Shift-Enter) only displays the first element of the array. I would like to see the whole array displayed, not just the first element.
Thanks Hui good practical one.
John@ReddyBay
want a massage then i close my excel work sheet. anybody help me regading this ?
@Nerinder
You can have a message appear as you save or Close by adding some code to the workbook module in the VBA Editor
Copy the code below
Alt F11
Find the Workbook object for your workbook
Paste the code into that module
You will now have to save the model as a *.xlsm or *.xlsb file
Private Sub Workbook_BeforeClose(Cancel As Boolean)a = MsgBox("My Message", vbOKOnly, "My Message Title")
End Sub
or
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
a = MsgBox("My Message", vbOKOnly, "My Message Title")
End Sub
This array equivalent of SUM will give the same result:
{=SUM(COUNTIF(B3,"*"&$E$3:$E$6&"*"))}
Back to what Sachin said...
How can I count the # of occurrences of each character from the list appears totaled?
So that:
B9 = 2 (2 "f"s)
B8 = 4 (2 "f"s + 1 "d")
For instance, this formula used on this example:
abcde*123*io*def<
Where the criteria is * ( ) !
Results in 3. It's only counting the "*" 2x.
The answer should be 4.
Can we make this work?
Everything I find online only works for one criterion or counts over a range.
The criteria should have been:
* ( ) !
Ok, lesson learned. I can't post less-than & greater-than symbols by their selves.
The criteria should include them, in order to make sense.