In this specific case I want to count how many times a set of variables combines in the same line, over 5 different columns, using excel, without macros:

How many times:

Alpha in Column 1 shows in the same line as Beta in Column 2)

Alpha in Column 1 shows in the same line as Beta in Column 3)

Alpha in Column 1 shows in the same line as Beta in Column 4)... repeated over 5 columns.

So far I'm using multiple countifs:

=COUNTIFS(Column 1, "Alpha", Column 2, "Beta") COUNTIFS(Column 1, "Alpha", Column 3, "Beta") COUNTIFS(Column 1, "Alpha", Column 3, "Beta")... for 5 columns

But this feels cumbersome and bruteforced...

Is there a more efficient/elegant way to solve this problem?

Example data in A1

Modality wk 1	Mon	Tue	Wed	Thu	Fri
Mod1	John	Karen	John	Joe	Lizzie
Mod2	Karen	Lizzie	Karen	John	John

Modality wk 2	Mon	Tue	Wed	Thu	Fri
Mod1	Joe	Karen	John	Karen	Lizzie
Mod2	Karen	Joe	Karen	Karen	John

To create a table like this, located in A10

Names	Mod1	Mod2
John	3	3
Karen	3	5
Lizzie	2	1
Joe	2	1

I'd have to use a formula like this in B11 and drag to fill the columns and rows (the $ were put to help with the drag and drop):

=COUNTIFS($A$1:$A$6,B$10,$C$1:$C$6,$A11) COUNTIFS($A$1:$A$6,B$10,$D$1:$D$6,$A11) COUNTIFS($A$1:$A$6,B$10,$F$1:$F$6,$A11) COUNTIFS($A$1:$A$6,B$10,$E$1:$E$6,$A11) COUNTIFS($A$1:$A$6,B$10,$B$1:$B$6,$A11)

CodePudding user response：

Maybe I'm over-simplifying, but on the other hand, maybe you are over-complicating. The result is exactly what you specified.

In B12, copy across and down:

=SUMPRODUCT(($B$2:$F$6=$A12)*($A$2:$A$6=B$11))

CodePudding user response：

Try below formula-

 =SUM(($B$2:$F$7=$A11)*($A$2:$A$7=B$10))

If you have LAMBDA() function to your excel then can try this dynamic approach. No need to drag down, it will spill results automatically. Just drag across right.

=BYROW($A$11:$A$14,LAMBDA(x,SUM(($B$2:$F$7=x)*($A$2:$A$7=B10))))