Search

# 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

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.

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

## Formula Forensics “The Series”

Formula Forensic Series:

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.

Thank you and see you around.

### Related articles:

 Written by Hui... Tags: countif(), sumproduct Home: Chandoo.org Main Page ? Doubt: Ask an Excel Question

### 14 Responses to “Formula Forensics 011. Lykes Formula”

1. Kurt says:

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

2. Sachin says:

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.

• Hui... says:

@Sachin
The question maybe should have been put as "How many of the small list appear in the text"

3. Hui... says:

@All

4. ahmed says:

Hi
In the forum Hui said that the all the links have been fixed. No they have not. They are still not working, sir

5. Hui... says:

@Ahmed
They are all working

6. Joe Carsto says:

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.

7. John Hackwood says:

Thanks Hui good practical one.
John@ReddyBay

8. Narinder Kumar says:

want a massage then i close my excel work sheet.   anybody help me regading this ?

• Hui... says:

@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 ```

9. Robert Mika says:

This array equivalent of SUM will give the same result:
{=SUM(COUNTIF(B3,"*"&\$E\$3:\$E\$6&"*"))}

10. MechEngr02 says:

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.

Can we make this work?

Everything I find online only works for one criterion or counts over a range.

• MechEngr02 says:

The criteria should have been:

* ( ) !

11. MechEngr02 says:

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.

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

### Get FREE Excel & Power-BI Newsletter

One email per week with Excel and Power BI goodness. Join 100,000+ others and get it free.