Count Unique Names, where each Name corresponds to Multiple Values
Count Unique Names (Multiple Occurrences of a Name are Counted as One) in column, where each Name corresponds to specific Multiple Values
Cell F6: Count Unique Names in column A, having corresponding column B values of both cells E2:E3 (ie. “ABC” & “KLM”).
COUNTS Unique Names in column A, where corresponding column B values Match Multiple Specific Values.
Formula specifically checks that a particular Name corresponds to each of the multiple cells of E2:E3, atleast once.
The same name (in column A) should correspond (in column B) to 2 values of E2:E3 – count how many of these unique names are present in column A.
If any cell in the Range E2:E3 is blank then the column A name should also correspond to a blank cell in column B to be counted.
Note: Count Unique Names means that Multiple Occurrences of a Name are Counted as One.
———————————————————————————————–
Cell F6 formula Counts Unique Names in column A, having corresponding column B values of both cells E2:E3 (ie. “ABC” & “KLM”).
Cell F6 formula:
This formula checks specifically 2 values of E2 & E3 in column B.
=SUM(IF(FREQUENCY( IFERROR(IF( B2:B30=E2, MATCH(A2:A30, IF(B2:B30=E3, IF(A2:A30<>””, A2:A30)),0)), “”), ROW(A2:A30)- ROW(A2)+1), 1))
Explanation – Cell F6 formula (Array Formula – Ctrl+Shift+Enter):
1) IF(B2:B30=E2, MATCH(A2:A30, IF(B2:B30=E3, IF(A2:A30<>””, A2:A30)), 0)): returns an array of numbers, FALSE values and #N/A errors – {8;FALSE; FALSE; 12;#N/A; FALSE; FALSE; FALSE; #N/A; FALSE; FALSE; FALSE; #N/A;#N/A; 24;6; FALSE;8;…} – the numbers indicate the position / row number of each non-blank value in column A, where its corresponding value in column B contains both E2 and E3 values. For example, the 1st number 8 is for the A2 cell (where B2 equals to either E2) and in the 8th position the cell A9 has same value as A2 and B9 equals either E3 (if B2 = E2 then B9 = E3). The number 8 is repeated in the 18th position which means that cell A2 value has another occurrence where the corresponding column B value, B19, is equal to B2. Similarly the next number 12 is in the 4th position (for cell A5) which means that cell B5 = cell E2 and the 12th position cell ie. cell A13 is the same value as cell A5 and cell B13 = E3. Error values occur where corresponding value(s) in column B (for a column A name) equal only one value of E2 or E3. The FALSE values represent where corresponding values in column B for a column A name do not match even one of the E2 & E3 values. IFERROR is used to convert error values to blank. This is the data_array argument of the FREQUENCY function.
2) ROW(A2:A30)-ROW(A2)+1: returns an array of values 1 to 29 – {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(IFERROR( IF(B2:B30=E2, MATCH(A2:A30, IF(B2:B30=E3, IF(A2:A30<>””, A2:A30)),0)), “”), ROW(A2:A30) -ROW(A2)+1): returns an array of numbers and zeros – {0;0; 0;0; 0;1; 0;2; 0;0; 0;1; 0;0; 0;0; 0;0;0;0; 0;0; 0;1; 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 Names having corresponding column B values of both cells E2:E3). For example, the 1st number 1 in the 6th position indicates that the number 6 (6th position in bins_array argument which is cell A7) appears once in the data_array argument of 1) above (cell A7 name of “Smith” where cell B7 corresponds to cell E3, appears once in the 16th position ie. in cell A17 & the corresponding value in cell B17 of “ABC” corresponds to cell E2). Similarly, the number 2 in the 8th position indicates that the number 8 (8th position in bins_array argument which is cell A9) appears twice in the data_array argument of 1) above (cell A9 name of “Steve” where cell B9 corresponds to cell E3, appears twice in the 1st & 18th position ie. in cells A2 & A19, and the corresponding value in cell B2 & B19 of “ABC” corresponds to cell E2). As explained, the Frequency of the bins_array argument number is returned only once at the position of its 1st occurrence.
4) IF(FREQUENCY(IFERROR( IF(B2:B30=E2, MATCH(A2:A30, IF(B2:B30=E3, IF(A2:A30<>””, A2:A30)),0)), “”), ROW(A2:A30)- ROW(A2)+1), 1): returns an array of 1s and FALSE values – {FALSE;FALSE; FALSE;FALSE; FALSE;1; FALSE;1; FALSE;FALSE; FALSE;1; FALSE;FALSE;…} – the non-blank cells in column A where corresponding value(s) in column B equal both E2 and E3 values, 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.
———————————————————————————————–
Related Formulas – illustrated in the Ebook of Excel Formulas.
Cell F9 Formula: Count Unique Names in column A, having corresponding column B values of all the 4 cells E2:E5 (ie. “ABC”, “KLM”, “DEF” & “XYZ”). While cell F6 formula checks each cell (E2 & E3) individually and may become unwieldy for a large number of cells, this formula can match any number of cells in column E as a combo, by just updating the range “E2:E5” in the formula. It is particularly useful where values of a large number of multiple cells in column E are to be matched.