Results 1 to 5 of 5

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

  1. #1

    in how much classes are the children together? Excel formula

    Register for a FREE account, and/
    or Log in to avoid these ads!

    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

    Name:  excel.png
Views: 134
Size:  10.4 KB

  2. #2
    Super Moderator NBVC's Avatar
    Join Date
    May 2011
    Mississauga, Canada
    Excel Version
    Excel 2016


    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:


    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 by NBVC; 2014-02-24 at 02:12 PM.

  3. #3
    Thanks!!! (=

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

  4. #4
    My mistake, it works great!

  5. #5
    Admin Alumnus Pecoflyer's Avatar
    Join Date
    Oct 2011
    Brussels Belgium
    Excel Version
    2010 on Xubuntu

Posting Permissions

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