# How to Count Combinations In a Set (Including dupes)

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

### 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

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().