User Rating: 5 / 5

Star ActiveStar ActiveStar ActiveStar ActiveStar Active
 

 

Count Unique Values where other column values Match ANY of the Array values

 

Related Links: 

1. Excel VLOOKP Function, with examples.

2. Left Lookup with VLookup Excel function.

3. Left Lookup, with Index, Match & Offset Excel functions.

4. Vlookup Multiple Values - Return MULTIPLE corresponding values for ONE Lookup Value.

 


 

Count Unique column A values (Multiple Occurrences of a Value are Counted Once) where column B values Match ANY of the Array values (ie. Array Criteria Match)

 

 

Column G formulas COUNT Unique column A values where column B values Match ANY of the Array values (ie. Array Criteria Match).

 

Cell G3: Count Unique column A Values (multiple occurrences of a value are counted once) where Column B = C2.

 

Cells G8:G10: Count Uniques in columns A & B combo where Column B = C2:C4 or column B = C2:E2 (ie col B is any of 3 values in C2:C4 / C2:E2 & then consider its values together with col A).

 

Cells G14:G17: Count Uniques in column A where Column B = C2:C4 or column B = C2:E2 (only uniques in col A if col B is any of the 3 values in C2:C4 / C2:E2). 

 

 

-----------------------------------------------------------------------------------------------

 

Cell G14 formula Counts Uniques in column A where Column B = C2:C4 (count only uniques in col A if col B is ANY of the 3 values in C2:C4).

 

 

Count Uniques, Array Criteria   

"Open Image in New Tab" for a full and clear view. 

 

 

Cell G14 formula: 

 

Result is 4 Uniques - ("AA";"ZZ"; "BB";"EE") - Note that "AA" (col A) occurs twice in combo with Col B, once with "YY" & then with "ZZ", but it is counted only once.

 

 

=SUM(IF( FREQUENCY( IF(B2:B20= TRANSPOSE(C2:C4), IF(A2:A20<>"", MATCH( A2:A20, A2:A20, 0))), ROW(A2:A20)- ROW(A2)+1), 1))  

 

 

Explanation - Cell G14 formula (Array Formula - Ctrl+ Shift+ Enter):

 

1) IF(B2:B20= TRANSPOSE(C2:C4), IF(A2:A20<>"", MATCH(A2:A20, A2:A20, 0))): returns an array of numbers and FALSE values comprising of 3 columns and 19 rows - {1,FALSE, FALSE; FALSE,2, FALSE; 1,FALSE, FALSE; FALSE,1, FALSE; FALSE, FALSE, FALSE; FALSE, FALSE,6; FALSE, FALSE, FALSE;...} -  the numbers indicate the relative position of each value in column A (where column B = C2:C4) with similar values having the same position. Blank cells, or where column B values are <> C2:C4, return FALSE. Note that the array is of 3 columns for each row, with each column representing C2, C3 & C4 respectively, so that the 1st row of column A represents "1,FALSE, FALSE" which indicates that cell B2 = cell C2, and the 2nd row of column A represents "FALSE,2, FALSE" which indicates that cell B3 = cell C3. This is the data_array argument of the FREQUENCY function.

 

2)  ROW(A2:A20)- ROW(A2)+1: returns an array of values 1 to 19 - {1;2; 3;4;5; 6;7;8; 9;10;…} - against which the Frequency of each column A positional value is determined ie. this is the bins_array argument of FREQUENCY function. Note that the FREQUENCY function enables calculating the FREQUENCY of a value ONLY ONCE (ie. if a value occurs multiple times, say 3, then its Frequency is returned as 3 but the value is considered only once. This enables determination of UNIQUE values.

 

3) FREQUENCY(IF(B2:B20= TRANSPOSE(C2:C4), IF(A2:A20<>"", MATCH(A2:A20, A2:A20,0))), ROW(A2:A20)- ROW(A2)+1): returns an array of numbers and zeros - {3;1;0; 0;0; 2;0; 1;0;0;0; 0;0;0; 0;0;0; 0;0;0} - the numbers indicate the frequency ie. the number of times each number in 1) above occurs (these are the frequency of row positions of non-blank column A cells where column B is equal to any of the 3 values in C2:C4). For example, the 1st number 3 in the 1st position indicates that the number 1 (1st position in bins_array argument) appears thrice in the data_array argument of 1) above (cell A2 value of "AA" appears thrice in cells A2, A4 & A5 & the corresponding values in cell B2, B4 & B5 of "YY" & "ZZ" are in cells C2 & C3). Similarly, the number 1 in the 2nd position indicates that the number 2 (2nd position in bins_array argument) is appears once in the data_array argument of 1) above (it appears once because for cell A3 value of "CC" appears only once where corresponding column B value = C2:C4 ie. cell B3 value of "ZZ" is in cell C3). As explained, the Frequency of the bins_array argument number is returned only once at the position of its 1st occurrence.

 

4) IF(FREQUENCY(IF(B2:B20= TRANSPOSE(C2:C4), IF(A2:A20<>"", MATCH(A2:A20, A2:A20,0))), ROW(A2:A20)- ROW(A2)+1), 1): returns an array of 1s and FALSE values - {1;1;FALSE; FALSE; FALSE; 1;FALSE;1; FALSE;FALSE;...} - the non-blank cells in column A where column B = C2:C4 with a frequency of 1 or more return 1, only once even for multiple occurrences of the same value, and used with the SUM function returns the total number of unique values in column A where column B = C2:C4.

 

 

 

Cell G4 Formula: Count Unique column A Values (multiple occurrences of a value are counted once) where Column B = C2. Array Formula (Ctrl+Shift+Enter), using COUNTIFS & cell reference (C2).

 

 

Cell G8:G10 formulas: Count Uniques in columns A & B combo where Column B = C2:C4 or column B = C2:E2 (ie col B is any of 3 values in C2:C4 / C2:E2 & then consider its values together with col A). Result is 5 Unique Combos - ("AA,YY"; "CC,ZZ";"AA,ZZ"; "BB,WW";"EE,YY").

 

Cell G8 Formula: Count Uniques in columns A & B combo where Column B = C2:C4. Array Formula, using COUNTIFS, TRANSPOSE & cell reference (C2:C4).

 

Cell G9 Formula: Count Uniques in columns A & B combo where Column B = C2:E2: Array Formula, using COUNTIFS & cell reference (C2:E2).

 

Cell G10 Formula: Count Uniques in columns A & B combo where Column B = {"YY","ZZ", "WW"}: Array Formula, using COUNTIFS & hard coded values ("YY", "ZZ","WW").

 

 

Cell G14:G17 formulas: Count Uniques in column A where Column B = C2:C4 or column B = C2:E2 (count only uniques in col A if col B is any of 3 values in C2:C4 / C2:E2). Result is 4 Uniques - ("AA";"ZZ"; "BB";"EE") - Note that "AA" (col A) occurs twice in combo with Col B, once with "YY" & then with "ZZ", but it is counted only once.

 

Cell G14 Formula: Already explained in detail above - Count Uniques in column A where Column B = C2:C4: Array Formula, using FREQUENCY, TRANSPOSE & cell reference (C2:C4).

 

Cell G15 Formula: Count Uniques in column A where Column B = C2:E2: Array Formula, using FREQUENCY & cell reference (C2:E2).

 

Cell G16 Formula: Count Uniques in column A where Column B = C2:C4: Array Formula, using FREQUENCY, MATCH & cell reference (C2:C4).

 

Cell G17 Formula: Count Uniques in column A where Column B = {"YY", "ZZ","WW"}: Array Formula, using FREQUENCY, MATCH & hard coded values ("YY","ZZ","WW"). 

 

 

 

Only One of the Multiple formulas is illustrated above. For full details and explanation refer to Ebook of Excel Formulas.