Count Uniques appearing in BOTH Columns or in 1 Column and NOT in Other
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 cell F2 & copy down. Ex, "Comp" & "Maths" are 2 subjects in both Semesters for "John" (cell F2);
-----------------------------------------------------------------------------------------------
Column F Counts the Number of Unique Subjects, for a Student, which are in BOTH Semester 1 and Semester 2.
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.
-----------------------------------------------------------------------------------------------
Related Formulas - illustrated in the Ebook of Excel Formulas.
Column G: Count the Number of Unique Subjects, for a Student, which are 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: Count the Number of Unique Subjects, for a Student, which are 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.
