Excel - Formula For Counting Entries Within Multip

Question

I have the following example matrix:

A   B   C   D   E   F   G   H   I   J   K   L   M   N   O   P   Q   R   S   T   
1   1   1   1   1   1   1   1   1   1   1   1   1   1   1   1   1   1   1   1
2   1       1                               1
3                   1   1       1   1   1                               1
4       1   1   1   1   1                   1      
5           1                   1               1   1   1       1           1

I am trying to count the number of rows for which at least one pair of non-sequential columns excluding row A includes entries in both columns. The pairs of columns are known but the distance between columns is not constant. In the current example, columns B & K and C & L are paired (distance or d = 9), E & I and F & J are paired (d = 4), and G & S and H & T are paired (d = 12). Notice that column D is not paired with any other column. This frequently occurs: usually the column distance between pairings changes after a column with no intrinsic pairing, e.g., A & E (d = 4), B & F (d = 4), C has no pairing, D & K (d = 7), E & L (d = 7), etc.

In the current example, the number of rows where at least one pair of of columns (as specified above) include entries is 3. For example, row 1 features entries for all pairs of columns, row 3 has entries for column pair F & J, as well as G & S, and row 4 has entries for column pairing C & L.

Here's a sample of an array formula I wrote:

=COUNT(IFS(SUBTOTAL(2,OFFSET(B1,ROW(B1:B5)-ROW(B1),0,1,COLUMNS(B1:C1)))>1,SUBTOTAL(2,OFFSET(A1,ROW(A1:A5)-ROW(A1),0,1,COLUMNS(A1))),SUBTOTAL(2,OFFSET(C1,ROW(C1:C5)-ROW(C1),0,1,COLUMNS(C1:D1)))>1,SUBTOTAL(2,OFFSET(A1,ROW(A1:A5)-ROW(A1),0,1,COLUMNS(A1)))))

It counts the number of entries for pairs of sequential columns (e.g., B & C, C & D). I am unsure how to update it so that it specifies pairs of non-sequential columns (e.g., B & K, C & L, E & I, etc. as specified above).

Here are a few conditions:

• You may not think SUBTOTAL is the most efficient option (e.g., MMULT abd SUBTOTAL might be more appropriate); however, I am limited to expressing this within a single excel formula (no VBA code, unfortunately). I cannot create new columns or rows either.
• The matrix entries are not necessarily 1 in the actual matrix, but they are whole numbers (ranging from 1-100).
• Within the full matrix, there are 8 column pairings with a distance = 9, 11 column pairings with a distance of 12, and 3 column pairings with a distance of 3.

Answer 1

Try this formula

=SUM(IF((
    IF(B1:B5>0,IF(K1:K5>0,1))+
    IF(C1:C5>0,IF(L1:L5>0,1))+
    IF(E1:E5>0,IF(I1:I5>0,1))+
    IF(F1:F5>0,IF(J1:J5>0,1))+
    IF(G1:G5>0,IF(S1:S5>0,1))+
    IF(H1:H5>0,IF(T1:T5>0,1))
) > 0, 1, 0))

You can add the remaining pairs in the above list, i have added the ones you have mentioned.