• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

in how much classes are the children together? Excel formula

bercod

New Member
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

 

Jake Collins

Active Member
Dear bercod

Given the classes being in B1-E5 and name 1 being in l2 and name 2 being in K2 then I think the following works.

=SUM(1*(MMULT(1*($B$1:$E$5=K2),{1;1;1;1})=MMULT(1*($B$1:$E$5=L2),{1;1;1;1})))

This is an array formula so needs to be entered using ctrl+shift+enter.

If you were to have more than four children per class then you would also need to extend the {1;1;1;1} to have 1's for each extra child place.
 

bercod

New Member
Thank you friends!

The result of JAKE did not work for me, but thank you anyway.
And way of SOMENDRA excellent work!

Thank you both (=
 
Top