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: 117
Size:  10.4 KB

  2. #2
    Super Moderator NBVC's Avatar
    Join Date
    May 2011
    Location
    Mississauga, Canada
    Posts
    1,429
    Articles
    0
    Excel Version
    Excel 2016
    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 by NBVC; 2014-02-24 at 01: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
    Super Moderator Pecoflyer's Avatar
    Join Date
    Oct 2011
    Location
    Brussels Belgium
    Posts
    1,317
    Articles
    0
    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
  •