User Rating: 5 / 5

Star ActiveStar ActiveStar ActiveStar ActiveStar Active
 

 

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

 

Count Uniques, Match Multi Values  

 

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.