Counting Unique Instances
I have an inventory of courses and course masters in a Smartsheet. A course (e.g., CAP 600) can have one or two modalities (BL or blended, OL or online, or BL and OL).
The PARENT row is the prefix, and each child is a row for a course. Oftentimes there are two rows for each course because there are two modalities of the course (e.g., CAP 600 below) while other times there is just one row (e.g., CAP 608) since it only has one modality (BL or blended).
I can count the number of children (equals 12), and I can use a formulae to determine if a course is unique (see the first row of CAP 600 is marked "Yes", but the second row of CAP 600 is marked "No"). I can then count the number of unique courses (equals 7). I can count the number of masters for courses/modalities that are completed (equals 5). And, finally, I can count the number of unique courses that are Active (equals 5, since CAP 609 and 610, while unique, are not "Active").
NUMBER OF CHILDREN = 12
NUMBER OF UNIQUE COURSES = 7
NUMBER OF COMPLETED MASTERS = 6
NUMBER OF UNIQUE COURSES THAT ARE ALSO ACTIVE = 5
However, what I cannot figure out how to do is count the number of courses that have a master for at least one modality. (This is different than just counting the number of "Completed".)
For this example, it would be 4. I do not want to count both of the Completed for CAP 600. I just want to count this course once, even though there are two masters, one for each modality. I would also count CAP 605 (it has a completed master for the OL modality at least), CAP 607 (it has at least the BL modality completed), and CAP 608 (even though it only has one modality, unlike others that have two modalities).
I would NOT count CAP 609 (it has no completed master) or CAP 610 (it has a completed master for the BL modality, but the course itself is Inactive).
Any idea what formula I could use to do this?
Best Answer
-
Yes. You would use CHILDREN references instead of referencing the entire column.
=COUNT(DISTINCT(COLLECT(CHILDREN(Course@row), CHILDREN([Active Status]@row), "Active", CHILDREN(Master@row), "Completed")))
Answers
-
Try something along the lines of...
=COUNT(DISTINCT(COLLECT(Course:Course, [Active Status]:[Active Status], "Active", Master:Master, "Completed")))
-
I have the Smartsheet setup so the parent is a row with the prefix (e.g., CAP), and the courses are the children. Your idea is fantastic! However, is there a way to use this just for the children, not the entire sheet?
So, in the example below, in the parent is the darker-shaded blue "MHA". I can calculate there are 6 unique courses (only 5 though have two modalities thus two rows, BL and OL; MHA 522 only has a BL modality).
There are only 5 unique AND active courses (MHA 405 has an "I" in the "Active Status" column, see green outline.)
There are only 4 unique AND active courses that have a master for at least one of the modalities MHA 422 and MHA 501 have a master for the OL-6w modality, but not the BL-6w, but it still counts both as having at least one. MHA.505 has a master for both the BL and OL, but it counts it only once. MHA 522 is only in one modality, and it has a master for it, so it counts it. It does NOT count MHA 405, since it is Inactive, and it does not count MHA 508, even though it is active, because it has no master in either BL or OL.
I would put the formula for MHA in the parent row (see red box), then another formula for HRM in the second red box near bottom of screenshot.
Ideally, it would be easier if I didn't have to specify rows in the formula because then I would have to update the formula for every prefix. Is there a way to count children then using the formula you came up with?
-
Yes. You would use CHILDREN references instead of referencing the entire column.
=COUNT(DISTINCT(COLLECT(CHILDREN(Course@row), CHILDREN([Active Status]@row), "Active", CHILDREN(Master@row), "Completed")))
-
YOU.
ARE.
AMAZING.
Worked perfectly.
Help Article Resources
Categories
Check out the Formula Handbook template!