I need to count the number of unique text values in a column that respect a certain condition in another column. The table is:
| Name | Beta |
|---|---|
| Lorem | Y |
| Lorem | Y |
| Ipsum | N |
| Dolorem | Y |
- The formula needs to be in a different google workbook than where the the above table is, so I will need to use the function importrange
- I understand that I cannot use the functions countunique or filter with importrange, so I am trying to use the function query
The expected result should be 2. I also placed the fomulas tried and data in this open google sheet: https://docs.google.com/spreadsheets/d/1L7q\_-spjoKuphxVXtDkbQn17rgbRNG9QaXaZIXKX7Dg/edit?usp=sharing
What formula should I use?
I tried to use both examples below but did not succeed:
- =UNIQUE(QUERY(A2:B5; "Select count (A) where B = 'Y' label count(A) ''"))
- =COUNTA(unique(QUERY(A2:B5; "Select (A) where B = 'Y'")))
The second fomula seems to work, but if I test it with no value respecting the condition B="Y" it still reports 1.
**: For the sake of this example and to keep it simple to explain, in the two examples that I tried I did not use the function importrange but I will actually need to use it in the final solution