User Rating: 5 / 5

Star ActiveStar ActiveStar ActiveStar ActiveStar Active
 

 

Count Uniques appearing in BOTH Columns or in 1 Column and NOT in Other

 

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 Uniques: (i) which appear in BOTH Columns; (ii) which appear in 1 Column and NOT in the Other Column

 

 

Each Student (column A) has Multiple Subjects in Semester 1  (column B) and in Semester 2 (Column C).

 

There may be Multiple times a Semester 1 Subject appears in column B for a Student, and similarly for Semester 2 in column C. 

 

Column F: Count the Number of Unique Subjects, for a Student, which are in BOTH Semester 1 and Semester 2 - enter Array Formula (Ctrl+Shift+Enter) in F2 & copy down. Ex, "Comp" & "Maths" are 2 subjects in both Semesters for "John" (cell F2);

 

Column G: Count the Number of Unique Subjects, for a Student, which is ONLY in Semester 1 and NOT in Semester 2 - enter Array Formula (Ctrl+Shift+Enter) in G2 & copy down. Ex, "English", "Social" & "Echo" are 3 subjects in Semester 1 and NOT in Semester 2, for "John" (cell G2);

 

Columns H: Count the Number of Unique Subjects, for a Student, which is ONLY in Semester 2 and NOT in Semester 1 - enter Array Formula (Ctrl+Shift+Enter) in H2 & copy down. Ex, "Stats" is 1 subject in Semester 2 and NOT in Semester 1, for "John" (cell H2); 

 

 

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

 

Column F Counts the Number of Unique Subjects, for a Student, which are in BOTH Semester 1 and Semester 2.

 

 

Count Uniques Both Columns

  

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

 

 

Cell F2 formula: 

 

 

=SUM(IF(FREQUENCY( IFERROR( MATCH(IF( A$2:A$30=E2, B$2:B$30, ""), IF(A$2:A$30=E2, IF(C$2:C$30<>"", C$2:C$30)), 0),""), ROW(B$2:B$30)- ROW(B$2)+1), 1))

 

 

Column F formula:

 

1. Count the Number of Unique Subjects, for a Student, which are in BOTH Semester 1 and Semester 2.

 

2. For Example, "Comp" & "Maths" are 2 subjects in both Semesters for "John" (cell F2).  

 

3. Enter Array Formula (Ctrl+Shift+ Enter) in cell F2 & copy down.

 

 

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

 

1) MATCH(IF( A$2:A$30=E2, B$2:B$30, ""), IF(A$2:A$30= E2, IF(C$2:C$30<>"", C$2:C$30)), 0): returns an array of numbers and Error values - {3;2; #N/A; #N/A; #N/A; 3;2; #N/A; 3; #N/A; #N/A;…} - the numbers indicate the relative position of each column B value where it appears in column C, for column A names which are same as cell E2 name. For example, the 1st number 3 indicates that the 1st value (cell B2) occurs at the 3rd position in column C (ie. cell C4) and every occurrence of cell B2 value in column B will return this. Similalry the 2nd number 2 indicates that the 2nd value (cell B3) occurs at the 2nd position in column C (ie. cell C3) and every occurrence of cell B3 value in column B will return this. Error values indicate when the column B value is not present in column C, and IFERROR is used to convert the error values to blanks. This is the data_array argument of the FREQUENCY function.

 

2) ROW(B$2:B$30)- ROW(B$2)+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 B 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 thrice, then its Frequency is returned as 3 but the value is considered only once, at the position of its 1st occurrence. This enables determination of UNIQUE values.

 

3) FREQUENCY(IFERROR( MATCH(IF( A$2:A$30=E2, B$2:B$30,""), IF(A$2:A$30=E2, IF(C$2:C$30<>"", C$2:C$30)), 0),""), ROW(B$2:B$30)- ROW(B$2)+1): returns an array of numbers and zeros - {0;2;3; 0;0; 0;0;…} - the numbers indicate the frequency ie. the number of times each column B value appears in column C where column A = cell E2 name. For example, the 1st number 2 in the 2nd position indicates that the number 2 (2nd position in bins_array argument) is repeated twice in the data_array argument. Similarly, the 2nd number 3 in the 3rd position indicates that the number 3 (3rd position in bins_array argument) is repeated thrice in the data_array argument. 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( MATCH(IF( A$2:A$30=E2, B$2:B$30,""), IF(A$2:A$30=E2, IF(C$2:C$30<>"", C$2:C$30)), 0),""), ROW( B$2:B$30)- ROW(B$2)+1), 1): returns an array of 1s and FALSE values - {FALSE; 1;1; FALSE; FALSE; FALSE; FALSE; FALSE;...} - the non-blank cells in column B where column A = E2 with a frequency of 1 or more in column C, 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. 

 

 

Column G: Counts the Number of Unique Subjects, for a Student, which is ONLY in Semester 1 and NOT in Semester 2. For Ex, "English", "Social" & "Echo" are 3 subjects in Semester 1 and NOT in Semester 2, for "John" (cell G2). Enter Array Formula (Ctrl+Shift+Enter) in G2 & copy down.

 

 

Column H: Counts the Number of Unique Subjects, for a Student, which is ONLY in Semester 2 and NOT in Semester 1. For Ex, "Stats" is 1 subject in Semester 2 and NOT in Semester 1, for "John" (cell H2). Enter Array Formula (Ctrl+Shift+ Enter) in H2 & copy down.

 

 

 

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