Distinct and Collect with Arrays

Art Schneiderheinze

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?

Paul Newcome

It is not possible because your ranges do not match in size. You are definitely going to need to incorporate a helper column or two.

Art Schneiderheinze

If I am able to collect all in one cell, what formula do I use then in another cell to count # of distinct in that list?

Helper1: PSY 101,PSY 102,ACC 233,PSY 316,PSY 102,ACC 233

Helper2: 4

Does DISTINCT work for one cell?

Paul Newcome

It does not which is where the challenge lies. My suggestion would be 3 helper columns where you grab the "A" or "I" and append it to the beginning of the course code using a formula.

=[Active Status]@row + "" + [Required1]@row

Do this in separate helper columns for each of the "Required" columns.

Then we can COUNT/DISTINCT/COLLECT based on "LEFT(@cell) = "A"".

=COUNT(DISTINCT(COLLECT([Required1 Helper]:[Required3 Helper], [Required1 Helper]:[Required3 Helper], LEFT(@cell) = "A")))