Distinct and Collect with Arrays

I am looking for the syntax for a COUNT, DISTINCT, COLLECT formula that will count number of distinct values in an array of cells, but only in rows that have a certain value in another column.

For example:

Active Status | Required1 | Required2 | Required3

A | PSY 101 | PSY 102 | ACC 233

I | BUS 212 | MHT 232 | ACC 314

A | PSY 316 | PSY 102 | ACC 233

The formula would first COLLECT the course codes in the array [Required1]1:[Required3]3, which would include 9 course codes (PSY 101, PSY 102, ACC 233, BUS 212, MHT 232, ACC 314, PSY 316, PSY 102, and ACC 233).

However, I want to exclude course codes if the Active Status value in that row is "I". So, the result would then be PSY 101, PSY 102, ACC 233, PSY 316, PSY 102, ACC 233).

But, in the end, I want it count number unique course codes in that collection. In this example, there would be 4 unique course codes (PSY 101, PSY 102, ACC 233, and PSY 316), since PSY 102 and ACC 233 in row 3 are repeats of course codes in row 1. Row 2 course codes are excluded because the Active Status isn't "A".

I have this:

=COUNT(DISTINCT(COLLECT([Required1]1:[Required3]3, [Active Status]1:[Active Status]3, "A"))), but that doesn't work. I am not sure how to use arrays and column in the same statement. I guess I could create a Helper column, but is there a way to have this in ONE formula?


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!