SOLVED

Create array from condition and count unique values

%3CLINGO-SUB%20id%3D%22lingo-sub-3205117%22%20slang%3D%22en-US%22%3ECreate%20array%20from%20condition%20and%20count%20unique%20values%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3205117%22%20slang%3D%22en-US%22%3E%3CP%3EI%20need%20to%20come%20up%20with%20a%20formula%20to%20create%20an%20array%20of%20values%20from%20Column%20A%20only%20from%20rows%20where%20Column%20B%20is%20a%20certain%20fixed%20value%2C%20then%20count%20the%20number%20of%20unique%20values%20in%20that%20array.%26nbsp%3B%20For%20example%2C%20if%20I%20had%20a%20spreadsheet%20with%20the%20following%20cells%20(starting%20at%20A1)%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CTABLE%20border%3D%221%22%20width%3D%22100%25%22%3E%3CTBODY%3E%3CTR%3E%3CTD%20width%3D%2250%25%22%3E2Q%3C%2FTD%3E%3CTD%20width%3D%2250%25%22%3EFALSE%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2250%25%22%3E2Q%3C%2FTD%3E%3CTD%20width%3D%2250%25%22%3ETRUE%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2250%25%22%3E2C%3C%2FTD%3E%3CTD%20width%3D%2250%25%22%3EFALSE%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2250%25%22%3E3A%3C%2FTD%3E%3CTD%20width%3D%2250%25%22%3ETRUE%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2250%25%22%3E3A%3C%2FTD%3E%3CTD%20width%3D%2250%25%22%3ETRUE%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2250%25%22%3E3B%3C%2FTD%3E%3CTD%20width%3D%2250%25%22%3ETRUE%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI'd%20create%20an%20array%20of%20Column%20A%20where%20Column%20B%20%3D%20TRUE.%26nbsp%3B%20My%20array%20would%20include%20values%20from%20A2%2C%20A4%2C%20A5%2C%20and%20A6%20(because%20the%20B-column%20values%20for%20those%20row%20numbers%20is%20TRUE)%2C%20which%20would%20be%20%7B2Q%2C3A%2C3A%2C3B%7D%2C%20and%20I'd%20then%20count%20the%20number%20of%20unique%20values%20in%20that%20array%2C%20which%20should%20output%20the%20digit%20'3'%20(the%20three%20unique%20values%20are%202Q%2C%203A%2C%20and%203B%2C%20for%20a%20total%20count%20of%203%20unique%20values).%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20know%20I%20could%20make%20it%20happen%20in%20SQL%2C%20but%20can't%20figure%20out%20how%20to%20do%20something%20similar%20using%20Excel%20functions.%26nbsp%3B%20Any%20tips%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-3205117%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EBI%20%26amp%3B%20Data%20Analysis%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EOffice%20365%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3205144%22%20slang%3D%22en-US%22%3ERe%3A%20Create%20array%20from%20condition%20and%20count%20unique%20values%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3205144%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1315790%22%20target%3D%22_blank%22%3E%40lp_rekor%3C%2FA%3E%26nbsp%3BTry%20this%3A%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-excel-formula%22%3E%3CCODE%3E%3DFILTER(A1%3AA6%2CB1%3AB6%3DTRUE)%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3205164%22%20slang%3D%22en-US%22%3ERe%3A%20Create%20array%20from%20condition%20and%20count%20unique%20values%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3205164%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1315790%22%20target%3D%22_blank%22%3E%40lp_rekor%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-applescript%22%3E%3CCODE%3E%3DCOUNTA(UNIQUE(FILTER(A1%3AA6%2CB1%3AB6%3D%22TRUE%22)))%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3EIs%20this%20what%20you%20are%20looking%20for%3F%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3205475%22%20slang%3D%22en-US%22%3ERe%3A%20Create%20array%20from%20condition%20and%20count%20unique%20values%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3205475%22%20slang%3D%22en-US%22%3EThanks%20folks%2C%20that's%20exactly%20what%20I%20needed!%3C%2FLINGO-BODY%3E
New 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 (New 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!