Count Unique Values / Duplicate Values in a Column
Count Unique Values (Multiple Occurrences Counted as One) or Duplicate Values in a Column
Column A contains values (text or numbers) which occur either only once or multiple times (Duplicate).
Column C illustrates how to count Unique Values (each distinct value is counted only once whether it appears once or multiple times) or Duplicate values (each distinct value is counted once only if it appears multiple times while ignoring values which appear only once).
———————————————————————————————–
Column C formulas Return COUNT of Unique or Duplicate Values in column A.
Count Unique Values
Cell C13 formula:
Counts number of Unique values (each distinct value is counted only once whether it appears once or multiple times).
=SUMPRODUCT(( A2:A20<>””)/COUNTIF( A2:A20, A2:A20&””))
Explanation – Cell C13 formula (Non-Array formula uses COUNTIF):
1) Using “COUNTIF(A2:A20, A2:A20&””)” returns an array of numbers – {2;1;2; 2;2; 2;3;2; 1;8; 3;3;8; 8;8; 8;8; 8;8} – where 8 indicates the number of blank cells and it (the number 8) is repeated for all positions of blank cells. Using “COUNTIF(A2:A20, A2:A20)” instead returns an array of numbers and zeros – {2;1;2;2; 2;2;3;2;1; 0;3;3;0;0;0;0; 0;0;0} – where zeros appear in all positions of blank cells and using this as a divisor will return #DIV/0! error values for zeros. (A2:A20<>””): returns TRUE (coverts to 1 in division) for non-blank cells and FALSE (converts to zero in division) for blank cells and dividing this with the “COUNTIF(A2:A20, A2:A20&””)” array will not return error values – this is the reason for using &””.
2) COUNTIF(A2:A20, A2:A20&””): returns an array of numbers – {2;1;2; 2;2; 2;3;2; 1;8; 3;3;8; 8;8; 8;8; 8;8} – where numbers indicate the number of occurrences of each value (will be minimum 1 for a value which occurs only once), and because there are 8 blank cells the number 8 is repeated eight times. (A2:A20<>””)/ COUNTIF( A2:A20, A2:A20&””): Dividing TRUE (ie. 1) with these number of occurrences returns {0.5;1; 0.5; 0.5;0.5; 0.5; 0.333333333333333; 0.5; 1;0; 0.333333333333333; 0.333333333333333; 0;0;0;0; 0;0;0}, and adding these (viz. the number 1 indicates single occurrence, the number 0.5 ie. 1/2+1/2 indicates 2 occurrences of a value, the number 0.333333333333333 ie. 1/3+1/3+1/3 indicates 3 occurrences of a value) and effectively return 1 for each separate or unique value irrespective of the number of times it occurs. The zeros in the array indicate blank cells. Using SUMPRODUCT returns the total number of unique values.
Cell C6 formula:
Counts Number of Values which appear only Once (if a value appears multiple times it is not considered)
=SUMPRODUCT(–( COUNTIF(A2:A20, A2:A20)=1)) [Formula]
Explanation – Cell C6 formula (Non-Array formula uses COUNTIF):
1) COUNTIF(A2:A20, A2:A20): returns an array of numbers and zeros – {2;1; 2;2; 2;2; 3;2; 1;0;3; 3;0; 0;0; 0;0; 0;0} – where numbers indicate the number of occurrences of each value in the column (will be minimum 1 for a value which occurs only once), and zeros indicate blank cells. For example, the 1st number 2 indicates that the 1st position (ie. cell A2) value of “AAAA” occurs twice in cell A2 & A6, the 2nd number 1 indicates that the 2nd position (ie. cell A3) value of “BBBB” occurs once only in cell A3, and so on. Equating this with “=1” will return TRUE for values which appear only once and using a double negation converts TRUE to 1 and using SUMPRODUCT returns the total. There are 2 values of “BBBB” and “3456” in cells A3 & A10 which appear only once.
———————————————————————————————–
Related Formulas – illustrated in the Ebook of Excel Formulas.
Cell C23 Formula: Counts number of Unique values (each distinct value is counted only once whether it appears once or multiple times) – Array formula (Ctrl+Shift+Enter) uses COUNTIF.
Cell C27 Formula: Counts number of Unique values (each distinct value is counted only once whether it appears once or multiple times) – Array formula (Ctrl+Shift+Enter) uses Frequency.
Cell C37 Formula: Counts number of Unique numericals (multiple occurrences of same number are counted as one) – valid ONLY for NUMBERS – NonArray formula using Frequency.
Cell C47 Formula: Counts number of Duplicate values (only multiple occurrences of same value are considered and counted as one, single occurrence of a value is ignored). NonArray formula uses COUNTIF.
Cell C52 Formula: Counts number of Duplicate values (only multiple occurrences of same value are considered and counted as one, single occurrence of a value is ignored). Array formula (Ctrl+Shift+Enter) uses Frequency.
Cell C56 Formula: Count Duplicate values (only multiple occurrences of same value are considered & counted as many times as they appear, single occurrences are ignored) – Array formula uses Frequency.