Two-value combination extraction formula?

[Alexinho]

I have a long formula as below which checks how many combinations of two specific subjects there are.

=IFNA(IF(AND(MATCH($A2,$Z$3:$AB$3,0),MATCH(C$1,$Z$3:$AB$3,0))=TRUE,1,0),0)+IFNA(IF(AND(MATCH($A2,$Z$4:$AB$4,0),MATCH(C$1,$Z$4:$AB$4,0))=TRUE,1,0),0)+IFNA(IF(AND(MATCH($A2,$Z$5:$AB$5,0),MATCH(C$1,$Z$5:$AB$5,0))=TRUE,1,0),0)+IFNA(IF(AND(MATCH($A2,$Z$6:$AB$6,0),MATCH(C$1,$Z$6:$AB$6,0))=TRUE,1,0),0)+IFNA(IF(AND(MATCH($A2,$Z$7:$AB$7,0),MATCH(C$1,$Z$7:$AB$7,0))=TRUE,1,0),0)+IFNA(IF(AND(MATCH($A2,$Z$8:$AB$8,0),MATCH(C$1,$Z$8:$AB$8,0))=TRUE,1,0),0)+IFNA(IF(AND(MATCH($A2,$Z$9:$AB$9,0),MATCH(C$1,$Z$9:$AB$9,0))=TRUE,1,0),0)+IFNA(IF(AND(MATCH($A2,$Z$10:$AB$10,0),MATCH(C$1,$Z$10:$AB$10,0))=TRUE,1,0),0)+IFNA(IF(AND(MATCH($A2,$Z$11:$AB$11,0),MATCH(C$1,$Z$11:$AB$11,0))=TRUE,1,0),0)+IFNA(IF(AND(MATCH($A2,$Z$12:$AB$12,0),MATCH(C$1,$Z$12:$AB$12,0))=TRUE,1,0),0)+IFNA(IF(AND(MATCH($A2,$Z$13:$AB$13,0),MATCH(C$1,$Z$13:$AB$13,0))=TRUE,1,0),0)+IFNA(IF(AND(MATCH($A2,$Z$14:$AB$14,0),MATCH(C$1,$Z$14:$AB$14,0))=TRUE,1,0),0)+IFNA(IF(AND(MATCH($A2,$Z$15:$AB$15,0),MATCH(C$1,$Z$15:$AB$15,0))=TRUE,1,0),0)+IFNA(IF(AND(MATCH($A2,$Z$16:$AB$16,0),MATCH(C$1,$Z$16:$AB$16,0))=TRUE,1,0),0)+IFNA(IF(AND(MATCH($A2,$Z$17:$AB$17,0),MATCH(C$1,$Z$17:$AB$17,0))=TRUE,1,0),0)+IFNA(IF(AND(MATCH($A2,$Z$18:$AB$18,0),MATCH(C$1,$Z$18:$AB$18,0))=TRUE,1,0),0)+IFNA(IF(AND(MATCH($A2,$Z$19:$AB$19,0),MATCH(C$1,$Z$19:$AB$19,0))=TRUE,1,0),0)+IFNA(IF(AND(MATCH($A2,$Z$20:$AB$20,0),MATCH(C$1,$Z$20:$AB$20,0))=TRUE,1,0),0)+IFNA(IF(AND(MATCH($A2,$Z$21:$AB$21,0),MATCH(C$1,$Z$21:$AB$21,0))=TRUE,1,0),0)+IFNA(IF(AND(MATCH($A2,$Z$22:$AB$22,0),MATCH(C$1,$Z$22:$AB$22,0))=TRUE,1,0),0)+IFNA(IF(AND(MATCH($A2,$Z$23:$AB$23,0),MATCH(C$1,$Z$23:$AB$23,0))=TRUE,1,0),0)+IFNA(IF(AND(MATCH($A2,$Z$24:$AB$24,0),MATCH(C$1,$Z$24:$AB$24,0))=TRUE,1,0),0)+IFNA(IF(AND(MATCH($A2,$Z$25:$AB$25,0),MATCH(C$1,$Z$25:$AB$25,0))=TRUE,1,0),0)+IFNA(IF(AND(MATCH($A2,$Z$26:$AB$26,0),MATCH(C$1,$Z$26:$AB$26,0))=TRUE,1,0),0)+IFNA(IF(AND(MATCH($A2,$Z$27:$AB$27,0),MATCH(C$1,$Z$27:$AB$27,0))=TRUE,1,0),0)+IFNA(IF(AND(MATCH($A2,$Z$28:$AB$28,0),MATCH(C$1,$Z$28:$AB$28,0))=TRUE,1,0),0)+IFNA(IF(AND(MATCH($A2,$Z$29:$AB$29,0),MATCH(C$1,$Z$29:$AB$29,0))=TRUE,1,0),0)+IFNA(IF(AND(MATCH($A2,$Z$30:$AB$30,0),MATCH(C$1,$Z$30:$AB$30,0))=TRUE,1,0),0)+IFNA(IF(AND(MATCH($A2,$Z$31:$AB$31,0),MATCH(C$1,$Z$31:$AB$31,0))=TRUE,1,0),0)+IFNA(IF(AND(MATCH($A2,$Z$32:$AB$32,0),MATCH(C$1,$Z$32:$AB$32,0))=TRUE,1,0),0)+IFNA(IF(AND(MATCH($A2,$Z$33:$AB$33,0),MATCH(C$1,$Z$33:$AB$33,0))=TRUE,1,0),0)+IFNA(IF(AND(MATCH($A2,$Z$34:$AB$34,0),MATCH(C$1,$Z$34:$AB$34,0))=TRUE,1,0),0)+IFNA(IF(AND(MATCH($A2,$Z$35:$AB$35,0),MATCH(C$1,$Z$35:$AB$35,0))=TRUE,1,0),0)+IFNA(IF(AND(MATCH($A2,$Z$36:$AB$36,0),MATCH(C$1,$Z$36:$AB$36,0))=TRUE,1,0),0)+IFNA(IF(AND(MATCH($A2,$Z$37:$AB$37,0),MATCH(C$1,$Z$37:$AB$37,0))=TRUE,1,0),0)+IFNA(IF(AND(MATCH($A2,$Z$38:$AB$38,0),MATCH(C$1,$Z$38:$AB$38,0))=TRUE,1,0),0)+IFNA(IF(AND(MATCH($A2,$Z$39:$AB$39,0),MATCH(C$1,$Z$39:$AB$39,0))=TRUE,1,0),0)+IFNA(IF(AND(MATCH($A2,$Z$40:$AB$40,0),MATCH(C$1,$Z$40:$AB$40,0))=TRUE,1,0),0)+IFNA(IF(AND(MATCH($A2,$Z$41:$AB$41,0),MATCH(C$1,$Z$41:$AB$41,0))=TRUE,1,0),0)+IFNA(IF(AND(MATCH($A2,$Z$42:$AB$42,0),MATCH(C$1,$Z$42:$AB$42,0))=TRUE,1,0),0)+IFNA(IF(AND(MATCH($A2,$Z$43:$AB$43,0),MATCH(C$1,$Z$43:$AB$43,0))=TRUE,1,0),0)+IFNA(IF(AND(MATCH($A2,$Z$44:$AB$44,0),MATCH(C$1,$Z$44:$AB$44,0))=TRUE,1,0),0)+IFNA(IF(AND(MATCH($A2,$Z$45:$AB$45,0),MATCH(C$1,$Z$45:$AB$45,0))=TRUE,1,0),0)+IFNA(IF(AND(MATCH($A2,$Z$46:$AB$46,0),MATCH(C$1,$Z$46:$AB$46,0))=TRUE,1,0),0)+IFNA(IF(AND(MATCH($A2,$Z$47:$AB$47,0),MATCH(C$1,$Z$47:$AB$47,0))=TRUE,1,0),0)

Essentially it matches the subject in column A and the subject in row 1 to each row in the array z:ab. It tallies the number of rows which contain both subjects in it. Due to the size of the formula, it can get a bit laggy, so I was wondering if there was any more efficient way of doing this?

 

[bebo021999]

I believe COUNTIFS or SUMPRODUCT can do the job, if you can share a mini sheet via XL2BB
(XL2BB - Excel Range to BBCode)
or gg drive

 

[Fluff]

Assumin you can never have the same subject twice on the sam row in cols Z:AB, how about in B2 only

=LET(f,FILTER(Z3:AB100,Z3:Z100<>""),MAKEARRAY(23,23,LAMBDA(r,c,IF(r>=c,"",SUM(--(MMULT((f=INDEX(A2:A24,r))+(f=INDEX(B1:X1,,c)),SEQUENCE(3,,,0))=2))))))

 

[Alexinho]

Here is the link to the sheet in google drive:

Subject Combos.xlsx

docs.google.com

 

[Fluff]

Have you tried th formula I suggested?

 

[Alexinho]

Assumin you can never have the same subject twice on the sam row in cols Z:AB, how about in B2 only

=LET(f,FILTER(Z3:AB100,Z3:Z100<>""),MAKEARRAY(23,23,LAMBDA(r,c,IF(r>=c,"",SUM(--(MMULT((f=INDEX(A2:A24,r))+(f=INDEX(B1:X1,,c)),SEQUENCE(3,,,0))=2))))))

Thanks for the formula, that seems to work very well (although I must admit I don't understand it at all!). If the number of subjects in the tally chart were to be more/less, how would the formula change?

 

[Fluff]

Do you mean in col A & row 1?
Also will those subjects always be in the same order as each other?

 

[Alexinho]

Do you mean in col A & row 1?
Also will those subjects always be in the same order as each other?

Yes, the ones in Col A and row 1. The subjects would be in alphabetical order however for other groups there may be some subjects omitted and some subjects added

 

[Fluff]

Ok, how about

=LET(vsub,A2:A24,hsub,B1:X1,f,FILTER(Z3:AB100,Z3:Z100<>""),MAKEARRAY(ROWS(vsub),COLUMNS(hsub),LAMBDA(r,c,IF(r>=c,"",SUM(--(MMULT((f=INDEX(vsub,r))+(f=INDEX(hsub,,c)),SEQUENCE(COLUMNS(f),,,0))=2))))))

 

[kvsrinivasamurthy]

TRY. Data taken
Table range A1:G7 . Option table range Z3:AB9, Change as required.

In B2

=LET(a,A2:A7,b,B1:G1,c,Z3:AB9,d,$Z$3:$AB$3,z,TEXTSPLIT(TEXTJOIN(",",FALSE,BYROW(a,LAMBDA(Ro,TEXTJOIN("_",FALSE,BYCOL(b,LAMBDA(clm,IF(ROWS($A$2:Ro)<COLUMNS($B$1:clm),SUM(1*(MMULT(1*((c=Ro)+(c=clm)),1*TRANSPOSE(COLUMN(d)>0))=2)),""))))))),"_",",",FALSE,1,""),IFERROR(VALUE(z),""))