in how much classes are the children together? Excel formula

D

daberco

New member
Joined
Feb 22, 2014
Messages
3
Reaction score
0
Points
0
I have several classes with children.
Each child participates in some classes.
I want to know how often participants two children in the same class.
I tried to do with the sumproduct function but I did not really succeed.

For example:

Class A: David, John, Bob, Ayalon
Class B: Elijah, Ayalon, Eviatar, Jorge
Class C: Ayalon, Joshua, David, Elijah
Class D: Jorge, Bob, Elijah, Eviatar
Class E: John, Joshua, Bob, David


The expected answer:
 name 1: Elijah, name 2:David, answer: 1
name 1: Bob, name 2: John, answer: 2
name 1: Joshua, name 2: Jorge , answer: 0


excel.png​
 
Try:

=SUMPRODUCT(--(MMULT(--(ISNUMBER(MATCH($B$1:$E$5,$K2:$L2,0))),TRANSPOSE(COLUMN($B$1:$E$5))^0)>1))

adjust ranges to suit.... then confirm with CTRL+SHIFT+ENTER not just ENTER and copied down.

Note: If the number of name columns is known and is not excessive, you can forego the CTRL+SHIFT+ENTER by replacing the TRANSPOSE part like so:

=SUMPRODUCT(--(MMULT(--(ISNUMBER(MATCH($B$1:$E$5,$K2:$L2,0))),{1;1;1;1})>1))

placing an equal number of semi-colon separated 1's as there are name columns in the database in this part: {1;1;1;1}
 
Last edited:
Thanks!!! (=

Why CNTL + SHFT + ENTR not work for me in Excel Starter?
 
You must log in or register to reply here.
Back
Top