Formula Forensics 011. Lykes Formula

Posted on February 9th, 2012 in Formula Forensics , Huis , Posts by Hui - 14 comments

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

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.

 

 

Written by Hui...
Tags: ,
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.

  3. Hui... says:

    @All
    The download and other links have been fixed

  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
    You may have to Clear your web browsers Cache

  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.
    The answer should be 4.

    Can we make this work?

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

  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.

Leave a Reply