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.