How can I simplify adding multiple countifs in excel?

Question

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.

So far I'm using multiple =COUNTIFS(), 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)

Actual data example: Example data.xls

Answer 1

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

Answer 2

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