Counting Instances Across Sheets
Answers
-
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! :)
-
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
-
Yes that is correct.
-
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?
-
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
-
=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.
-
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.
-
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?
-
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!!!!
-
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?
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- 10.7K Get Help
- 63 Global Discussions
- 68 Industry Talk
- 385 Announcements
- 3.5K Ideas & Feature Requests
- 55 Brandfolder
- 125 Just for fun
- 50 Community Job Board
- 464 Show & Tell
- 40 Member Spotlight
- 44 Power Your Process
- 28 Sponsor X
- 234 Events
- 7.3K Forum Archives
Check out the Formula Handbook template!