How to Count Combinations In a Set (Including dupes)

Question

Trying to figure out a formula where I have 5 columns of text data. I want to count how many times combinations of data in 2 contiguous columns appear in each row.

Example data:

	A	B	C	D	E
1	A	A	A	B	C
2	A	B	B	A	A

In the example data above there are 8 range pairs (4 ranges per row):

	Paired Cells 1	Paired Cells 2	Paired Cells 3	Paired Cells 4
Row 1	{A1:B1}	{B1:C1}	{C1:D1}	{D1:E1}
Row 2	{A2:B2}	{B2:C2}	{C2:D2}	{D2:E2}

From the paired ranges there are eight unique value pairs:

Unique Paired Values	Count	Paired Cell Locations
{A,A}	3	A1:B1   B1:C1   D2:E2
{A,B}	2	C1:D1   A2:B2
{B,C}	1	D1:C1
{B,B}	1	B2:C2
{B,A}	1	C2:D2
&nbsp	Count Total: 8	Paired Cells Total: 8

Answer 1

Formula 1: Separate Counts For Each Row

This formula provides a separate count for each row's combinations.

={ "ROW #", "COMBO", "COUNT";
    ARRAYFORMULA(SPLIT(
      BYROW(
        QUERY( 
          TRANSPOSE(SPLIT(JOIN(";",
            BYROW(A:E, LAMBDA(rB, IF(INDEX(rB,,1)="", IFERROR(1/0), JOIN(";",
              MAP(SEQUENCE((COUNTA(rB)-1)), LAMBDA(r, 
                ROW(rB) & "," & INDEX(rB,,r) & INDEX(rB,,r+1)))))))),";")),
          "Select Col1, COUNT(Col1) group by Col1 label COUNT(Col1) '' "),
        LAMBDA(rC, JOIN(",",rC))),",")) }

^{Formula 1: Separate Counts For Each Row}

 

Formula 2: Combined Counts Across All Rows

This formula gives a combined count for combinations across all rows.

={ "COMBO", "COUNT";
   QUERY(
     TRANSPOSE(SPLIT(TEXTJOIN(";", TRUE, 
       BYROW(A:E, LAMBDA(rB, IF(INDEX(rB,,1)<>"",
         JOIN(";", MAP(SEQUENCE((COUNTA(rB)-1)), LAMBDA(r, 
           INDEX(rB,,r) & INDEX(rB,,r+1)))),
         IFERROR(1/0) )))),
       ";")),
     "select Col1, COUNT(Col1) group by Col1 label COUNT(Col1) ''"
   )
 }

^{Formula 2: Combined Counts Across All Rows}

Answer 2

Use let(), offset(), map(), flatten() and query(), like this:

=let( 
  next_, lambda(cell, offset(cell, 0, 1)), 
  pair_, lambda(cell, join("-", cell, next_(cell))), 
  query( 
    flatten(map(A1:D2, pair_)), 
    "select Col1, count(Col1) group by Col1", 
    0 
  ) 
)

See let(), offset(), map(), flatten() and query().