Count Unique values in a Column, with Condition(s)
Count Unique values in a Column, with Condition(s) – each distinct value is counted only once irrespective whether it appears once or multiple times
Column A contains values (text or numbers) which occur either only once or multiple times (Duplicate).
Columns B and C contain Conditions – these also occur once or multiple times corresponding to each occurrence of column A value. In cell E4 formula the aim is to COUNT Unique Occurrences of Column A values where corresponding Column B value is <> “Local”.
———————————————————————————————–
Column E formulas Return COUNT of Unique of Values in column A, subject to Specific Condition(s).
Cell E4 formula:
Count Unique values in col A where co B is <> “Local” – count each distinct value only once whether it appears once or multiple times.
=SUM(IF( FREQUENCY(IF( B2:B20<>”Local”, IF(A2:A20<>””, MATCH( A2:A20, A2:A20, 0))), ROW( A2:A20)- ROW(A2)+1), 1))
Explanation – Cell E4 formula (Array Formula uses Frequency):
1) IF(B2:B20<>”Local”, IF( A2:A20<>””, MATCH(A2:A20, A2:A20, 0))): returns an array of numbers and FALSE values – {1;2; 3;1; FALSE;3; FALSE; FALSE; 1;10; FALSE; FALSE;…} – the numbers indicating the relative position of each value in column A (where column B <> “Local”) with similar values having the same position. Blank cells, or where column B values are “Local”, return FALSE. This is the data_array argument of the FREQUENCY function.
2) ROW(A2:A20)- ROW(A2)+1: returns an array of consecutive numbers 1 to 19 – {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.
3) FREQUENCY(IF( B2:B20<>”Local”, IF(A2:A20<>””, MATCH(A2:A20, A2:A20,0))), ROW(A2:A20)- ROW(A2)+1): returns the array – {3;1; 2;0; 0;0; 0;0; 0;1;0; 0;0; 0;0;0; 0;0; 0;0} – the 1st number 3 indicates that cell A2 value (“AAA”) occurs 3 times in the column at A2, A5 & A10 and 3 is returned in the position of 1st occurrence whereas zero is returned in positions 4 & 9 (2nd & 3rd occurrence). Note that the FREQUENCY function returns the Frequency ie. number of times each value appears in column A, but this number or Frequency is returned for each value only at the position of its 1st occurrence in the column. So those values with a frequency of 1 or more return a number only at one position (ie. 1st position of occurrence) even for multiple occurrences of the same value. If a value occurs multiple times, say thrice, then its Frequency is returned as 3 but the value is considered only once. This enables determination of UNIQUE values. Using the IF statement with the FREQUENCY function returns an array of 1s and FALSE values – {1;1;1; FALSE; FALSE; FALSE;…} – the non-blank cells in column A where column B <> “Local” 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 where column B <> “Local”.
———————————————————————————————–
Related Formulas – illustrated in the Ebook of Excel Formulas.
Cell E18 Formula: Count Unique values in column A where column B is <> “Local” AND <> “State” (ie. column B is neither “Local” NOR “State”) – Array Formula uses Frequency.
Cell E20 Formula: Count Unique values in column A where column B is “CA” – Array Formula uses Frequency.
Cell E22 Formula: Count Unique values in column A where column B is “CA” – Array Formula uses COUNTIFS.
Cell E33 Formula: Count Unique values in column A where column B is “CA” and column C is “Y” – Array Formula uses Frequency.
“Search” Condition in column B:
Cell E37 Formula: Count Unique values in column A, wherein “A” appears in Column B: Array formula uses Frequency.
Cell E42 Formula: Count Unique values in column A, wherein “A” appears in Column B and column C = “Y”: Array formula uses Frequency.
Cell E44 Formula: Count Unique values in column A, wherein “A” appears in Column B and column C = “Y”: Array formula uses COUNTIFS.
Cell E46 Formula: Count Unique values (applicable ONLY to Numbers) in column A, wherein “A” appears in Column B: Array formula uses Frequency.