# Thread: in how much classes are the children together? Excel formula

1. ## in how much classes are the children together? Excel formula

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

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

2. 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}

3. Thanks!!! (=

Why CNTL + SHFT + ENTR not work for me in Excel Starter?

4. My mistake, it works great!

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•