Counting Instances Across Sheets

Counting Instances Across Sheets

I am building a dashboard for each of the colleges at my university. I originally created a Smartsheet for each college that lists all of the courses in each of the programs in the college, documenting whether or not we have a course master (or template) built for each modality (BL=blended, OL=online). These sheets are organized by course code prefixes. So, there will be a parent row with the prefix, and children for each course (and modality) with that prefix.


In this example, you will see there is one course with ECO prefix (ECO 440) that has two modalities, BL-6w and OL-6w. Both modalities have Completed course masters (see the "Development Status" column).


For this sheet, I am able to determine how many unique and active courses are in each prefix. For ECO 440, the course is "Active" (see "A" in "Active Status" column), so the # of unique and active courses in the ECO prefix is 1, and 100% of the courses (there is still just the one) that has at least one modality with a Completed course master.


HCL 301 also has two modalities, but only one has a Completed master (OL-6w). But, it is still counted as having at least one Completed master HSC 311, however, has neither a BL or OL master, so it is excluded from the total count, even though it is a unique and active course.

Anyway, I have this all figured out for prefixes.


I am now creating a new Smartsheet that lists the programs in the college, and the required courses (e.g., ECO 440) and electives (e.g., FIN 444). I want to be able to report to college deans what percentage of courses (required and electives) have at least one course master.

One of the programs is Health Care Leadership, B.S. I entered all of the Required courses. (There are no electives.) I want to write a formula(s) so that this sheet looks at every course code in the other sheet and displays the percentage of those courses that have at least one Completed master.


Just to make things more difficult, not all courses Required are in the one college's other sheet. For example some courses are offered in one college and others, mostly electives, are offered by other colleges, so they would be on a similar but different sheet.


I am not sure how to have Smartsheet look at each course code in the list of Required courses (separated by commas), then look up that course code in the other sheet, and then determine, if it finds it, if the course has at least one master (either in BL or OL). I likely will also want to have a separate columns that can also report back if each of those courses has a BL master and/or an OL master. (Deans may ask me, "How many courses in our inventory for this program have OL masters? How many have BL masters? How many have both? How many have neither?)


I think I might be able to do this if it was just once course (find ECO 440 in the sheet, report back if it has a BL, OL, or BL & OL master). But, is there an easy way to do this if the courses are in a list, separated by a comma (perhaps using hidden columns)?


Simple example:

Let's say the Health Care Leadership, B.S. only has 3 required courses, which are all offered by the same college (so are on the same other course sheet). In the "Percentage Required" column, it might say 2, or 66% of the courses required have at least one master. To do this, Smartsheet would reference the other sheet, search for those three course codes (which have different prefixes), and determine if either a BL or OL master is Completed. In this example, it found two courses have at least one master, one has none. So, it reports back 2, or 66% of the Required courses have course masters.


I may actually want to have THREE columns, percentage of courses with a BL master, percentage of courses with an OL master, and a third with a percentage that have both. The Deans would LOVE to have this kind of information at their fingertips.


Thanks to anyone who can guide me in a good direction!

Best Answers

Previous1

Answers

  • Paul NewcomePaul Newcome ✭✭✭✭✭

    How many different required/electives could there be for any given program?

  • edited 06/22/20

    I would say, to be safe, there would be 15 or less required courses, electives, etc. for each program. One program, however, has 30 required courses, since each course is only a few weeks.

  • Paul NewcomePaul Newcome ✭✭✭✭✭

    Ok. So how would you feel about either adding 30 columns (can be moved to the far right and then hidden) and automating everything or adding 15 columns and automating everything except for the one with 30?


    Basically we would need to parse your "HCL 301,HCL 311,HCL 316..............." out across the row. That way you could still enter your courses in the comma delimited string as you have them now, and everything else will happen automatically on the back-end.


    It is a fair amount of columns, but the overall setup would be relatively straightforward. I already have a parsing solution built for parsing down a single column. It shouldn't be too much modification to parse across a row.

  • The 15 to 30 columns would be in the college sheet (list all courses by prefix, first screenshot in original post) or in the second sheet (listing the programs, second screenshot)?

    I'm open to try whatever would make this possible :)

  • Paul NewcomePaul Newcome ✭✭✭✭✭

    It would go in the sheet in the second screenshot so we can take your string of "HCL 301,HCL 311,HCL 316..............." and parse it out across a row so each course is in it's own cell.


    I did just have another thought... You want to be able to separate Required vs Electives. What is the total number of EACH? We will need to add that many columns.


    So the first step is parsing. You have your two "string" columns of Required and Electives.

    Next you will want to add columns to cover the maximum number of Required and Elective Courses and label them as

    [Required1], [Required2], [Required3], [Elective1], [Elective2], [Elective3], so on and so forth.


    Then in the [Required1] column, you would use:

    =LEFT([email protected], FIND(",", [email protected]) - 1)


    Put the following in the [Required2] column and dragfill to cover the rest of the [Required#] columns:

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


    We want to do the same for the Electives, so in [Elective1] enter:

    =LEFT([email protected], FIND(",", [email protected]) - 1)


    And in [Elective2] (then dragfill on over):

    =IFERROR(LEFT(SUBSTITUTE([email protected] + ",", JOIN($[Elective1]@row:[Elective1]@row, ",") + ",", ""), FIND(",", SUBSTITUTE([email protected] + ",", JOIN($[Elective1]@row:[Elective1]@row, ",") + ",", "")) - 1), "")


    This will give us:


    Now that we have our lists parsed out, we can move on to getting your total counts. I will start another comment for that though as I do need to do a little testing for that one. I have a pretty good idea on how to make it work but want to be sure.

  • Paul NewcomePaul Newcome ✭✭✭✭✭

    So in your counts (using your above screenshots), you want to only count "HCL 301" one time even though it has both modalities, but you would want to show a percentage of 50% since only one of those modalities shows a "Completed" master? Or were you wanting to show a count of 2 and 50%, or something else?

  • edited 06/23/20

    Your first question---yes, required and electives, separately. Again depends on the program, but this can be as few as 1 to as many as 30 of each.

    I thought a little about this, and I think it would be easier to determine

    1. What percentage of REQUIRED courses in the program have
      1. a BL master?
      2. an OL master?
      3. a BL and OL master?
    2. What percentage of ELECTIVE courses in the program have
      1. a BL master?
      2. an OL master?
      3. a BL and OL master?

    Hopefully, this doesn't mean 6x the number of columns!

  • Paul NewcomePaul Newcome ✭✭✭✭✭

    "Your first question---yes, required and electives, separately. Again depends on the program, but this can be as few as 1 to as many as 30 of each."

    This means you would need up to 60 columns using the above parsing solution.


    To get your counts, we don't need to create 6 times the number of columns. Just 6 columns (one for each metric). I will get back to you on those exact formulas once I have gotten some testing done. Work is picking up, so it may not be until tomorrow.

  • Hi Paul. Any chance to play around with your idea yet?

  • Paul NewcomePaul Newcome ✭✭✭✭✭

    I haven't quite gotten it figured out yet, but I feel I am close. Just to be sure... The counts above are only for those where the [Development Status] is "Completed" correct?

  • Paul NewcomePaul Newcome ✭✭✭✭✭

    Another question...

    In reference to the following sample data:

    Course..........Modality

    301................BL

    301................OL

    311................BL

    316................OL


    Assuming all of the masters are complete, how would you want this counted?

    1. BL master - 1
    2. OL master - 1
    3. a BL and OL master - 1


    OR

    1. BL master - 2
    2. OL master - 2
    3. a BL and OL master - 0


    OR

    1. BL master - 2
    2. OL master - 2
    3. a BL and OL master - 1


  • edited 06/29/20

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

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

    311................BL Completed

    316................OL Completed

    318...............BL Completed

    318...............OL In Progress

    320...............BL No Master

    320...............OL No Master


    For 301, it would be 1 BL master, 1 OL master, and 1 for having both a BL and OL master.

    For 311, it would be 1 Bl master, 0 OL masters, and 0 for not having both a BL and OL master.

    For 316, it would 0 BL, 1 OL, and 0 for not having both a BL and OL master.

    Also, I added 318 as an example, it would be 1 BL (since it is Completed), but 0 OL (since currently the master is In Progress of being developed). See 320, that I added. It has two rows, but the count is 0 for BL and OL, since while there are rows, the Development Status is "No Master".


    A course isn't always offered in BL and OL, so there may only be a row for BL or OL. For other courses, there may be both BL and OL rows, but only one, both, or neither are marked Completed.


    Reasons why: A dean would want to know if 301 is a required course, for example, he could offer it in a BL format or an OL format (because there is a master for both). If 311 was a required course, he could only offer it in BL. There is no OL master. And for 316, only OL, since there is no BL master.

    The BL/OL part is an easy calculation, based on the other two. If BL and OL, then BL/OL. If BL and no OL, then not BL/OL, etc.

  • Paul NewcomePaul Newcome ✭✭✭✭✭

    Ok. I was assuming you wanted your totals on this sheet:


    Which is also where the parsing solution was put into place:


    Based on that assumption, I was thinking your totals would be 6 additional columns and row by row like so:


    But your most recent comment leads me to believe that I am now mistaken. Can you please confirm the layout of how you want these counts and where you want them?

  • You are correct. The totals would appear on that sheet.

    For Health Care Leadership , B.S., as an example, there are 13 required courses and 0 electives. To the right, per your solution, there would be columns into which the required courses would be parsed. Then I am guessing additional columns that would total the number of courses with BL masters Completed of the 13, number of courses with OL masters Completed of the 13, and a column with the number of courses with both BL and OL masters of the 13.

    So, just making up numbers, it may say there are 12 courses with completed BL masters, 8 with completed OL masters, and 7 with both completed BL and OL masters. (I can do the math to show this as a percentage of courses required in the program: 92% with BL, 67% with OL, and 58% with both BL and OL.

    But, yes, all in one sheet .

  • Paul NewcomePaul 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 [email protected] 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 NewcomePaul 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 NewcomePaul 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([email protected], FIND(",", [email protected]) - 1)

    Added to Required2:

    =IFERROR(LEFT(SUBSTITUTE([email protected] + ",", JOIN($[Required1]@row:[Required1]@row, ",") + ",", ""), FIND(",", SUBSTITUTE([email protected] + ",", 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([email protected] <> "None", LEFT([email protected], FIND(",", [email protected]) - 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([email protected] + ",", JOIN($[Electives1]@row:[Electives1]@row, ",") + ",", ""), FIND(",", SUBSTITUTE([email protected] + ",", 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?

  • 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 NewcomePaul 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!!!!

  • Paul NewcomePaul Newcome ✭✭✭✭✭

    Awesome!! Happy to help! 👍️

  • 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?

Sign In or Register to comment.