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!

Find more posts tagged with

Accepted answers

Art Schneiderheinze

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

Paul Newcome

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

Try putting the above in [Required2] and [Electives2] and dragfilling over. Basically, if it finds a blank, then it will make the rest of the cells blank, but if it doesn't find a blank then it will run the parsing formula.

Paul Newcome

And use this in [Required1] column:

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

That should clear up the error when there is only one course.

All comments

Paul Newcome

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

Art Schneiderheinze

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 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.

Art Schneiderheinze

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 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(Required@row, FIND(",", Required@row) - 1)

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

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

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

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

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

=IFERROR(LEFT(SUBSTITUTE($Electives@row + ",", JOIN($[Elective1]@row:[Elective1]@row, ",") + ",", ""), FIND(",", SUBSTITUTE($Electives@row + ",", 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 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?

Art Schneiderheinze

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

What percentage of REQUIRED courses in the program have
1. a BL master?
2. an OL master?
3. a BL and OL master?
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 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.

Art Schneiderheinze

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

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

Art Schneiderheinze

Yes, Completed.

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

BL master - 1
OL master - 1
a BL and OL master - 1

BL master - 2
OL master - 2
a BL and OL master - 0

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

Art Schneiderheinze

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

Art Schneiderheinze

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 .

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.