Frequency of characters in any order are found in a range

[tasty]

I have been attempting to find a formula that counts the number of times the characters in a cell are found in any of the other strings of letters in a range. (What has stumped me is that I do not want just the exact matches of the characters in the same order, but rather, to count all instances when the characters from the reference cell are found even if they are separated by other characters. In my examples below in column A I have the strings of characters and column B is where I want to display the number of times they appear. My goal is to build a formula that will show me that abce appears twice... once as just abce, and then again as apart of abcde. Then bce appears 3 times in bce, abce, and abcde.
Example 1:
Strings / Count
abce / 2
abcde / 1
ae / 3
bce / 3

I have also formatted my data into strings of 0s and 1s as text to see if going about it this way might make more sense, to no avail. In this case 00101 appears twice, once in 00101, and again in 01111.
Example 2:
Strings / Count
01010 / 2
01111 / 1
00101 / 2

 

[Eric W]

Welcome to MrExcel!

Interesting problem, how about:

Book1
	A	B
1	abce	2
2	abcde	1
3	ae	3
4	bce	3
Sheet2

Cell Formulas
Range	Formula
B1:B4	B1	=SUM(--(MMULT(--ISNUMBER(FIND(MID(A1,TRANSPOSE(ROW(INDIRECT("1:"&LEN(A1)))),1),$A$1:$A$4)),ROW(INDIRECT("1:"&LEN(A1)))^0)=LEN(A1)))
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[jolivanes]

Eric handed you what you asked for.
Another way (not what you asked for!)

Sub Maybe()
Dim j As Long, a As String, c As Range, i As Long, x As Long, xx As Long
    For j = 1 To Cells(Rows.Count, 1).End(xlUp).Row
    a = Cells(j, 1).Value
        For Each c In Range("A1:A4")
            For i = 1 To Len(a)
                If InStr(c, Mid(a, i, 1)) <> 0 Then x = x + 1
            Next i
                If x = Len(a) Then xx = xx + 1
                    x = 0
                Next c
            Cells(j, 1).Offset(, 1).Value = xx
        x = 0
    xx = 0
    Next j
End Sub

 

[tasty]

Thanks for the welcome and your solution! This absolutely solved what I was looking for, and I wish I had asked for help much sooner.

Would it be possible to add one more layer to this? I realize that the formula tells me how many times this pattern appears, but the way my data is provided to me it groups all of the same exact strings of patterns and counts how many times it occurred in a Column C. In the demo example abce shows up, in this case 400 times as a unique pattern, could we modify your formula in B to sum the volume amounts instead of count the instances that the pattern appears? Below it would 600 times (400 as abce, and 200 as abcde)
Strings / Count / Volume
abce / 600 / 400
abcde / 200 / 200
ae / 1200 / 500
bce / 700 / 100

 

[Eric W]

That's just a minor change:

Book1
	A	B	C
1	abce	600	400
2	abcde	200	200
3	ae	1100	500
4	bce	700	100
Sheet2

Cell Formulas
Range	Formula
B1:B4	B1	=SUM((MMULT(--ISNUMBER(FIND(MID(A1,TRANSPOSE(ROW(INDIRECT("1:"&LEN(A1)))),1),$A$1:$A$4)),ROW(INDIRECT("1:"&LEN(A1)))^0)=LEN(A1))*$C$1:$C$4)
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[tasty]

Yes, that is it thank you Eric!