**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).**

**"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**.