# 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

**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.

### Leave a Reply

Using external software packages to manage your spreadsheet risk [Part 4 of 4] |
How would you customize Excel after installing? [poll] |

## 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.