SOLVED

New Contributor

# 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

# Re: Create array from condition and count unique values

@lp_rekor Try this:

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

best response confirmed by lp_rekor (New Contributor)
Solution

# Re: Create array from condition and count unique values

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

Is this what you are looking for?

# Re: Create array from condition and count unique values

Thanks folks, that's exactly what I needed!