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?
Answers
-
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.
-
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?
-
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")))
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.9K Get Help
- 441 Global Discussions
- 139 Industry Talk
- 471 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 68 Community Job Board
- 497 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!