SOLVED

Create array from condition and count unique values

Copper Contributor

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):

 

2QFALSE
2QTRUE
2CFALSE
3ATRUE
3ATRUE
3BTRUE

 

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_rekor Try this:

=FILTER(A1:A6,B1:B6=TRUE)

 

best response confirmed by lp_rekor (Copper Contributor)
Solution

@lp_rekor 

=COUNTA(UNIQUE(FILTER(A1:A6,B1:B6="TRUE")))

Is this what you are looking for? 

Thanks folks, that's exactly what I needed!
1 best response

Accepted Solutions
best response confirmed by lp_rekor (Copper Contributor)
Solution

@lp_rekor 

=COUNTA(UNIQUE(FILTER(A1:A6,B1:B6="TRUE")))

Is this what you are looking for? 

View solution in original post