I have the following example matrix:
A B C D E
1 1 1 1 1
2 1 1
3 1 1
4 1 1
5 1 1
My task was to count the number of rows for which at least one pair of columns excluding row A included entries in both columns (e.g., B & C make one pair, D & E make another). In the above example, the answer is 3: row 1 includes entries for both pairs of columns, row 3 includes entries for the first pair of columns (B & C) and row 4 has entries for the second pair of columns (D & E). That's 3 rows which include data for at least one designated pair of columns.
The following array formula achieves the desired results:
=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(D1,ROW(D1:D5)-ROW(D1),0,1,COLUMNS(D1:E1)))>1,SUBTOTAL(2,OFFSET(A1,ROW(A1:A5)-ROW(A1),0,1,COLUMNS(A1)))))
The problem is, I have about 30 pairs of columns (some non-adjacent) and a relatively large dataset (up to 10,000 rows). I am looking for a more efficient way to achieve the desired result in an Excel formula. Note that I wish to avoid creating new rows or columns, which is why I used an array formula
Not quite sure why you're including column A in the example if it is in fact irrelevant.
As such, for the range B1:E5:
=SUMPRODUCT(N(MMULT((B1:D5<>"")*(C1:E5<>"")*ISEVEN(COLUMN(C1:E5)-MIN(COLUMN(C1:E5))),ROW(INDEX(A:A,1):INDEX(A:A,COLUMNS(B1:D5)))^0)>0))
Extend the ranges as required, though note that the part A:A within the INDEX constructions should remain constant.
No need for CSE with the above.
Regards