Counting Instances Across Sheets

24

Answers

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    Ok. So the biggest thing I need to know is that if one of the courses has both completed, which of the 3 below would be the correct count? Basically I need to know the grouping criteria for the counts.

    1 BL

    1 OL

    1 Both


    1 BL

    1 OL

    0 Both


    0 BL

    0 OL

    1 Both


    So our sample course offerings:

    301................BL Completed

    301................OL Completed


    And our sample layout:


    Exactly what number(s) would go in which column(s) based on the sample data above?

  • Health Care Leadership, B.S.has 13 required courses:

    HCL 301,HCL 311,HCL 316,HCL 320,HCL 326,HCL 350,HCL 410,HCL 416,HCL 421,HCL 426,HCL 431,HCL 499,PJM 300

    So, the questoin is, how many of those 13 courses have a completed BL master? How many have a completed OL master? How many have both a completed BL and OL master?

    The NLU Colleges and Programs sheet would reference the PS Course Master Record sheet for each course to check the status of the masters:

    The columns it would look at are [Subject + Course]@row by prefix and Modality@row and [Development Status]@row.

    HCL 301 has a Completed OL master, but it does not have a Completed BL master. So, for this course, it would add this to the total of required courses for the program: 1 BL, 1 OL, 0 BL/OL. This means, for HCL 301, there is NOT a completed BL master, there is a completed OL master. Thus, HCL 301 does not have a completed BL and OL master.

    For HCL 311, it would be the same, BL master is completed, OL is not. Thus, it does not add to total number of courses with BL and OL masters.

    HCL 316, however, does have a completed BL and a completed OL master. So it adds 1 to the total number of required courses with BL masters. It adds 1 to the total number of OL masters. And, since it has a completed BL and OL master, it adds 1 to total of required courses with both a BL and OL master.

    After these three courses, the totals would be:

    • BL: 1 (only HCL 316 so far has a completed BL master)
    • OL: 3 (all three have completed OL masters)
    • BL/OL: 1 (since only HCL 316 so far has both a completed BL and OL master)

    Sorry if I made this seem more confusing than it is! :)

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    Ok. That answers my question. What I needed to know was how to count for if a course has both. Based on the above if we look at HCL 316 ONLY, the counts would be

    BL: 1

    OL: 1

    Both: 1

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    Ok. That actually makes this much easier.

    Insert a text/number "helper" column on this sheet:


    In my example I called it [Modality Helper]. Enter this formula into row 1 and dragfill down...

    =IF(COUNTIFS([Subject + Course]$1:[Subject + Course]@row, [Subject + Course]@row) = 1, JOIN(COLLECT(Modality:Modality, [Subject + Course]:[Subject + Course], [Subject + Course]@row, [Development Status]:[Development Status], "Completed"), "/"))


    Then go back to your sheet where you want the counts and insert your six columns.

    [Required - BL], [Required - OL], [Required - BL/OL], and the same three for the Electives.


    [Required - BL]:

    =COUNTIFS({Course List Subject + Course}, CONTAINS(@cell, [Required1]@row:[Required4]@row), {Course List Modality Helper}, CONTAINS("BL", @cell))


    [Required - OL]:

    =COUNTIFS({Course List Subject + Course}, CONTAINS(@cell, [Required1]@row:[Required4]@row), {Course List Modality Helper}, CONTAINS("OL", @cell))


    [Required - BL/OL]:

    =COUNTIFS({Course List Subject + Course}, CONTAINS(@cell, [Required1]@row:[Required4]@row), {Course List Modality Helper}, AND(CONTAINS("BL", @cell), CONTAINS("OL", @cell)))


    Then you would use the same formulas for the Elective Counts except changing [Required1]@row:[Required4]@row to [Elective1]@row:[Elective4]@row (this range is referencing the columns used to parse out the courses).


    Of course you are also going to have more than 4 columns for the Required and Electives, so you will need to update the columns to reflect what you actually have on your sheet.


    This will give you a live count that will adjust as you mark the various Masters as "Completed".


    Feel free if you need a more detailed explanation or screenshots.

  • Created all the columns.

    Added to Required1:

    =LEFT(Required@row, FIND(",", Required@row) - 1)

    Added to Required2:

    =IFERROR(LEFT(SUBSTITUTE($Required@row + ",", JOIN($[Required1]@row:[Required1]@row, ",") + ",", ""), FIND(",", SUBSTITUTE($Required@row + ",", JOIN($[Required1]@row:[Required1]@row, ",") + ",", "")) - 1), "")


    Works fine (see red outline). However, when a Requiredx column is blank (there are no more required courses in the list; see green outline)) the next column displays the first course in the list, and does this for all remaining columns for Required (see blue outline).



    Added to Electives1:

    =IF(Electives@row <> "None", LEFT(Electives@row, FIND(",", Electives@row) - 1), "")

    I had to add the IF for "None" because it wasn't working if there was a None for the electives.

    Added to Electives2:

    =IFERROR(LEFT(SUBSTITUTE($Electives@row + ",", JOIN($[Electives1]@row:[Electives1]@row, ",") + ",", ""), FIND(",", SUBSTITUTE($Electives@row + ",", JOIN($[Electives1]@row:[Electives1]@row, ",") + ",", "")) - 1), "")


    Also works fine until there are no more courses, then it repeats the first one in the list (or "none" if this was the first in the list for electives).

  • Also, what do you think would be a good way to handle situations when either Required or Electives contains courses offered by another college.

    For example, say the Required courses are HCL 301, HCL 306, HCL 308, and SPY 415

    The three HCL courses on the class list for the Professional Studies college course list sheet, while the SPY 415 is on the Education College course list sheet.

    Would the formulas in the Required1, Required2, etc. Electives1, Electives2, etc. just need to be extended to also include a cell reference to the second course list sheet?

  • Art Schneiderheinze
    Answer ✓

    I also noticed, now that I copied/pasted the formula across the rows/columns, if there is only ONE required course or elective, I get an #INVALID VALUE in the Required1, Required2, Electives1, Electives2 columns

  • Those worked perfectly!

    Now, just need to consider when a program requires a course or has an elective that is offered by another college.

    For Required1, I currently have:

    =COUNTIFS({PS-CMR-SubjectCourse}, CONTAINS(@cell, [Required1]@row:[Required35]@row), {PS-CMR-ModalityHelper}, CONTAINS("BL", @cell))


    Would I just add the same thing, to check the other college's course list?

    =SUM(COUNTIFS({PS-CMR-SubjectCourse}, CONTAINS(@cell, [Required1]@row:[Required35]@row), {PS-CMR-ModalityHelper}, CONTAINS("BL", @cell)) + COUNTIFS({ED-CMR-SubjectCourse}, CONTAINS(@cell, [Required1]@row:[Required35]@row), {ED-CMR-ModalityHelper}, CONTAINS("BL", @cell))

    I added the ED because this would be the cell reference on the other college's sheet, in case a course is not on the one sheet but is on the other sheet. Is this the best way to do it?

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    The only thing different I would do would be that you don't need the SUM function. You can just add the COUNTIFSs together.

    =COUNTIFS(.....................) + COUNTIFS(.....................)

  • I set this all up. Randomly and manually checked 3 of the programs, one of which has Required courses in two different colleges

    And the results were correct! :)

    I cannot tell you how thankful I am for this!!!!

  • Found a snag with one of the colleges.

    When determining the number of BL masters, it found 8, but there are only 7 required courses.

    Why?

    The Modality(Helper) correctly counted two BL masters for the same course as two separate BL masters:

    ACC 202 BL-6w [Completed]

    ACC 202 BL-10w [Completed]

    ACC 202 OL [Completed]

    ACC 202 is a required course for a program. So, it should count 1 BL, 1 OL, and 1 BL/OL. But it is actually reporting 2 BL, 1 OL, 1 BL/OL.

    Both may be completed, but the Modality(Helper) formula counts its as two, when it should be one.

    =IF(COUNTIFS([Subject + Course]$1:[Subject + Course]@row, [Subject + Course]@row) = 1, JOIN(COLLECT(Modality:Modality, [Subject + Course]:[Subject + Course], [Subject + Course]@row, [Development Status]:[Development Status], "Completed"), "/"))

    Any easy/quick adjustment to only count 1 of the two BL or OL, if there are actually two for a course?

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!