Results 1 to 6 of 6
  1. #1
    Silver Lounger
    Join Date
    Jan 2001
    Location
    West Long Branch, New Jersey, USA
    Posts
    1,921
    Thanks
    6
    Thanked 9 Times in 7 Posts

    sum across various conditions

    Hi All,

    I'm trying to get a formula for cell D4 (and, by extension, all of D4:F403) of the Family-Recital sheet. You can see my failed attempts in D6 and D7 of that sheet (there were many others that got nowhere that have since been deleted).

    What I'm looking for in D4 is the number of times that any student that's part of the family identified on the row (4 in this case) appears in any class that is part of the recital identified in the column (D in this case). That way, the parents, grandparents, etc. know if they need/want to go to that recital if one or more of their children are in that recital regardless of which class(es) those kids are in.

    Students are matched to families in the Student-Family sheet while Students belong or not to a class roster (with the associated Class #) according to the Class Rosters sheet. Finally the Class Names sheet says whether the Class # is part of Recital 1, 2, and/or 3 in columns G-I (there are other ways to get this last piece of info but those are harder to search).

    As an intermediate step in developing what I needed, I created 3 columns in the Student-Family sheet (see columns S-U) to sum up how many times each student was in Recital 1, 2, 3. I haven't developed that since I was looking for the "all-in-one" formula per above. Failing that (which I have so far), I figured I could do a SUMIF searching the Family # in column A of the Family-Recital sheet in column C of the Student-Family sheet and SUMIF on column S for recital 1 in the Family-Recital sheet, on column T for recital 2 in the Family-Recital sheet, etc.

    The info in the above paragraph (how many times each student appears in each recital) may or may not be useful to the users of this spreadsheet, so I didn't want go down that path unless necessary. I've taken my formula from D7 of the Family-Recital sheet and, with some modifications to focus just on the student, added that to S2 of the Student-Family sheet; still no good.

    Attached is the spreadsheet as it stands now (with a few unnecessary sheets deleted to get the file down to acceptable size). The formulas in D6 and D7 of the Family-Recital sheet don't seem to be working. They are pretty much the same with 1 small inconsequential difference and both return 0 (which is incorrect). When I try to partially evaluate the formulas with F9, I sometimes get a "Formula Too Long" message - yet the formula as a whole does seem to give a value.

    TIA

    Fred
    Attached Files Attached Files

  2. #2
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,820
    Thanks
    133
    Thanked 481 Times in 458 Posts
    Hi Fred

    Well, it was a simple request I guess.
    Yeah right!
    And in Excel2003 too!

    I have attached a file with my solution as a starting point for you.

    Essentially, the formula I placed in [D4] was
    =countAppearances(D$3,$A4)

    ..where D$3 = "Recital #1"
    ..and $A4 = Family #

    Hope this gives you a start.

    zeddy
    Attached Files Attached Files

  3. #3
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,820
    Thanks
    133
    Thanked 481 Times in 458 Posts
    Hi Fred

    Further to my previous post:
    Yes, I used some Custom Functions in my proposed solution.

    In my attached file, I actually placed a longer formula, by checking first whether there are any members of a Family #, i.e. like this:
    in cell [D4]
    Code:
    =IF(ISNA(MATCH($A4,'Student-Family'!$C$2:$C$401,FALSE)),"",countAppearances(D$3,$A4))
    The Custom Function uses the Recital Number and Family number as inputs.
    =countAppearances(recital, family#)

    What this does is:
    1. From the family number, get the list of students in the family
    (I used another Custom Function to get this - see cells in column [I] on sheet [Familty-Recital]

    2. get the list of Classes that are involved in the Recital.
    This is given in column [D] of sheet [Recital #1-Routine Info]
    To simplify things, I assigned a range name for this recital1_Classes
    (You will need to create recital2_Classes and recital3_Classes for the other Recitals etc etc)

    3. Check if each family member is in any class involved in the Recital.
    If they are in more than one class in the Recital, count each class as an 'appearance'.

    Well, its a starting point for you.

    To simplify my testing of the reported Appearances, on sheet [Class Rosters] I highlighted the class columns that related to Recital#1.
    (blue cells in row 1); I hid some of the class columns (you can unhide them again).
    This allowed me to easily add some 'additional' students (blue cells) to test the counts.

    Please note, I am travelling to New York this week.
    It will be a no-computer trip.
    So, unless you report back quickly, it may be some while before I can repond again.

    zeddy
    Last edited by zeddy; 2013-11-04 at 12:12.

  4. #4
    Silver Lounger
    Join Date
    Jan 2001
    Location
    West Long Branch, New Jersey, USA
    Posts
    1,921
    Thanks
    6
    Thanked 9 Times in 7 Posts
    Hi Zeddy,

    Thks for the reply. Will not have a chance to take a look at your suggestions at least until tonight (8am here on the East Coast of the US) so you may be gone.

    But if you do get this before you leave AND if you have time while in NYC, take a chance on "A Gentleman's Guide to Love and Murder" on Broadway. I just saw it 2 days ago, knowing almost nothing about. it. It was funny, clever - a lot of fun (more than I had trying to get that formula).

    Will take a look ASAP.

    BTW: Who said this was easy? If I could do it in a DB program, I would. Person for whom this is does not have a DB program (yet).

    Fred

  5. #5
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,820
    Thanks
    133
    Thanked 481 Times in 458 Posts
    Hi Fred

    Leaving for NYC on Thursday, so will be here in Newcastle UK tomorrow.
    Many thanks for the heads up on the Broadway show!
    Sounds like fun. I have a busy schedule but will check it out.

    zeddy

  6. #6
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,820
    Thanks
    133
    Thanked 481 Times in 458 Posts
    Hi Fred

    NYC was fantastic! Had no time to see any shows but had the best steaks ever.

    Now, did you get the chance to look at my posted file?
    It may look complicated, but if there is anything you want me to explain I'd be very happy to go through it step-by-step.
    ..Or maybe some others on this forum could look at other possible solutions.

    zeddy

Posting Permissions

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