Forum Discussion
Create array from condition and count unique values
I need to come up with a formula to create an array of values from Column A only from rows where Column B is a certain fixed value, then count the number of unique values in that array. For example, if I had a spreadsheet with the following cells (starting at A1):
| 2Q | FALSE |
| 2Q | TRUE |
| 2C | FALSE |
| 3A | TRUE |
| 3A | TRUE |
| 3B | TRUE |
I'd create an array of Column A where Column B = TRUE. My array would include values from A2, A4, A5, and A6 (because the B-column values for those row numbers is TRUE), which would be {2Q,3A,3A,3B}, and I'd then count the number of unique values in that array, which should output the digit '3' (the three unique values are 2Q, 3A, and 3B, for a total count of 3 unique values).
I know I could make it happen in SQL, but can't figure out how to do something similar using Excel functions. Any tips?
3 Replies
- lp_rekorCopper ContributorThanks folks, that's exactly what I needed!
- OliverScheurichGold Contributor
- Riny_van_EekelenPlatinum Contributor