Hi, I've been battling this problem for a while now and any assistance would be greatly appreciated.

What I am looking to do is to use a formula to search a list of numbers

e.g:

021
215
904
227
222
312

stored as text, and COUNT the number of occurrences that TWO specific digits appear together, regardless of their order within the cell.

e.g:

the pair of digs “1” and “2” in the list above appears 3 times (021, 215, 312).

So, I would like the formula to basically return a result of 3 when I search for those two digits. I would then duplicate the formula for any other pairs I wanted to search for in the list.

Here’s what I have so far after searching a lot on the forum for answers, but it is not completely working:

=SUMPRODUCT((LEN(A1:A6)-LEN(SUBSTITUTE(UPPER(A1:A6),UPPER("1"&"2"),"")))/LEN("1"&"2"))

The problem is that this doesn’t return all variants and I’m not sure how to make it do so.


The SECOND issue I anticipate is that, as in the example above, how would I deal with a number like “222”? As far as I’m concerned, this would count as 2 times the digits “2” and “2” appear together in the same cell as a pair. But how do I search for unique entries and add them? It would be no different if the number was “212”. In an example like that I would say that the pairs “1” and “2” appears twice in the example, the digit “1” with the first “2” and the digit “1” with the last “2”.


So essentially my results will end up looking like this:

Digits 0 and 1 have paired = 1 time
Digits 1 and 2 have paired = 3 times

Etc, etc, where each statement was as the result of a formula that searched that list for one specific pairing of digits.


And I would like to say thanks in advance for taking the time to help me with this.


Duran