Feb 23 2022 08:41 AM
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?
Feb 23 2022 08:44 AM
Feb 23 2022 08:49 AM
SolutionFeb 23 2022 09:19 AM
Feb 23 2022 08:49 AM
Solution=COUNTA(UNIQUE(FILTER(A1:A6,B1:B6="TRUE")))
Is this what you are looking for?