Count Unique Values where other column values Match ANY of the Array values
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 G14: Count Uniques in column A where Column B is any of the 3 values in C2:C4 (only uniques in col A if col B is any of the 3 values in C2:C4).
———————————————————————————————–
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).
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.
———————————————————————————————–
Related Formulas – illustrated in the Ebook of Excel Formulas.
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”).