Formula Forensics No. 002 – Joyces Question

Last week Joyce asked a question on the Chandoo.org, Comment 24.

I’m wondering if there’s a way to count the number of occurrences of words when they’re all in a cell? Like this:
A1: “Windows NT, Networking, Firewalls, Security, TL, Training”
A2: “Networking, Networking, Training, Security, TL, Training”
A3: “Security, TL, Firewalls, Security, Networking, Windows NT”

Joyce

Hui responded with an Array Formula:

=SUM(LEN(A1:A3)-LEN(SUBSTITUTE(A1:A3,C10,””)))/LEN(C10)

As the formula is an Array Formula it is entered with Ctrl Shift Enter.

Setup the Problem

Copy the Data Above into Cells A1:A3 or download the example file here: Example File (all Excel versions)

Enter the text string Security into cell C10

And array enter the formula

D10: =SUM(LEN(A1:A3)-LEN(SUBSTITUTE(A1:A3,C10,””)))/LEN(C10)

Cell D10 should now display the value 4, which is the number of times the Word Security, appears in the Range A1:A3.

Pull The Formula Apart

Lets take a look inside this and see how it works

We will break this formula apart and look at each section independently and then put the answers back together.

=SUM(LEN(A1:A3)-LEN(SUBSTITUTE(A1:A3,C10,””)))/LEN(C10)

In a cell below the data

D13: =LEN(A1:A3) but don’t press Enter, Press F9

Excel displays ={57,56,57}

This is the number of characters in each cell A1:A3

ie: A1 has 57 characters, A2 has 56 characters, A3 has 57 characters,

You can check this manually by typing =Len(A1) into any spare cell

=SUM(LEN(A1:A3)-LEN(SUBSTITUTE(A1:A3,C10,””)))/LEN(C10)

In another cell below the data

D15: =LEN(SUBSTITUTE(A1:A3,C10,””)) but don’t press Enter, Press F9

Excel displays ={49,48,41}

What this section does is measure the length of each cell in A1:A3 but only after substituting the word being searched for from C10 with ””, which is a zero length string.

So the second array is shorter than the first Array, by X times the length of the word in C10

=SUM(LEN(A1:A3) – LEN(SUBSTITUTE(A1:A3,C10,””)))/LEN(C10)

Next we add up the difference between the two arrays

So you can see we have two arrays of numbers

Array 1 = {57,56,57}

Array 2 = {49,48,41}

If we subtract Array 2 from Array 1

= {57-49, 56-48, 57-41}

= {8, 8, 16}

We can do this in Excel to Check

In Cell D17 enter

=LEN(A1:A3)-LEN(SUBSTITUTE(A1:A3,C10,””)) and press F9

Excel displays: = {8, 8, 16}

=SUM(LEN(A1:A3) – LEN(SUBSTITUTE(A1:A3,C10,””)))/LEN(C10)

The next part is to sum these up

Obviously the sum of 8, 8 & 16 is 32

We can check that

D21: =SUM(LEN(A1:A3)-LEN(SUBSTITUTE(A1:A3,C10,””))) and press F9

Excel displays: 32

=SUM(LEN(A1:A3) – LEN(SUBSTITUTE(A1:A3,C10,””)))/LEN(C10)

The final part of this is to divide the sum (32 in this case) by the length of the text in C10 “Security” = 8 Characters

=32 / 8

= 4

Correct – The number of times Security appears in the cells A1:A3 is 4.

OTHER POSTS IN THIS SERIES:

You can learn more about how to pull Excel Formulas apart in the following posts

Formula Forensic 001 – Tarun’s Problem

WHAT FORMULAS WOULD YOU LIKE EXAMINED ?

If you have any formulas you would like explained please feel free to leave a post here or send me an email:

If the formula is already on Chandoo.org or Chandoo.org/Forums or even forbid another web site, simply send the link to the post and a Comment or ID No. number if appropriate.

If sending emails please attach an Excel file with the formula and data