Count Unique values in a column, subject to Multiple Conditions – AND / OR
Count Unique values in column A subject to multiple AND/OR conditions
Cell J2 formula – Count Unique values in column A subject to multiple AND/OR conditions: (i) If ANY column B, D or F has “Y” (ie. if atleast one of these columns contains “Y”); (ii) If NONE of the columns C, E & G contain “Out” (ie. not even one of these 3 columns should have “Out”); (iii) Dates in column H are between I2 (Start Date) & I3 (End Date).
Cell J2 formula checks each column individually for the conditions – works well for limited number of columns.
———————————————————————————————–
Cell J2 formula Counts Unique values in column A subject to multiple AND/OR conditions: (i) If ANY column B, D or F has “Y” (if atleast one of these columns contains “Y”); (ii) If NONE of the columns C, E & G contain “Out” (not even one of these 3 columns should have “Out”); (iii) Dates in column H are between I2 (Start Date) & I3 (End Date).
Cell J2 formula:
This formula checks each column individually for the conditions – works well for limited number of columns.
=SUM(IF( FREQUENCY( IF((B2:B20=”Y”)+ (D2:D20=”Y”)+ (F2:F20=”Y”), IF((C2:C20<>”Out”)*( E2:E20<>”Out”)*( G2:G20<>”Out”), IF((H2:H20 >=I2)*( H2:H20<=I3), IF(A2:A20<>””, MATCH(A2:A20, A2:A20, 0))))), ROW(A2:A20)- ROW(A2)+1), 1))
Explanation – Cell J2 formula (Array Formula – Ctrl+Shift+Enter):
1) (B2:B20=”Y”)+ (D2:D20=”Y”)+ (F2:F20=”Y”): this checks if any of the 3 columns B / D / F contain “Y”, using the OR logic, and returns an array of numbers and zeros with the numbers indicating the number of columns in each row which contain “Y” – {2;0; 3;3; 2;2; 1;0; 2;0; 0;0; 0;0; 0;0; 0;0;0}. (C2:C20<> “Out”)*(E2:E20<> “Out”)*(G2:G20<> “Out”): this checks if each of the 3 columns C / E / G do not contain “Out”, using the AND logic and returns an array of 1s and zeros with 1s indicating the rows in which neither of the 3 columns contain “Out” – {1;1;0; 1;1;1; 1;1;0; 1;1;1; 1;1;1; 1;1; 1;1}. (H2:H20>=I2)*( H2:H20<=I3): this checks the dates in column H to be within the cell I2 and I3 dates range and returns 1 where the column H dates fall inbetween these 2 dates and zeros for dates which are out of this range – {1;1;1; 1;1; 1;0; 1;1;0;0; 0;0; 0;0; 0;0; 0;0}.
2) IF((B2:B20=”Y”)+ (D2:D20=”Y”)+ (F2:F20=”Y”), IF((C2:C20<> “Out”)*( E2:E20<> “Out”)*( G2:G20<> “Out”), IF((H2:H20 >=I2)*( H2:H20 <=I3), IF(A2:A20<>””, MATCH(A2:A20, A2:A20, 0))))): returns an array of numbers and FALSE values – {1;FALSE; FALSE;4; 1;4;FALSE; FALSE;…} – the numbers represent the positions / row numbers where all the 3 conditions as mentioned in 1) above are satisfied. This is the data_array argument of the FREQUENCY function.
3) ROW(A2:A20)- ROW(A2)+1: returns an array of values 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. Note that the FREQUENCY function enables calculating the FREQUENCY of a value ONLY ONCE (ie. if a value occurs multiple times, say twice, then its Frequency is returned as 2 but the value is considered only once. This enables determination of UNIQUE values.
4) FREQUENCY(IF((B2:B20=”Y”)+ (D2:D20=”Y”)+(F2:F20=”Y”), IF((C2:C20 <>”Out”)*( E2:E20 <>”Out”)*( G2:G20 <>”Out”), IF((H2:H20>=I2)*( H2:H20 <=I3), IF(A2:A20<>””, MATCH( A2:A20, A2:A20,0))))), ROW(A2:A20)- ROW(A2)+1): returns an array of numbers and zeros – {2;0;0; 2; 0;0; 0;0;0; 0;0; 0;0; 0; 0;0; 0;0; 0;0} – the numbers indicate the frequency ie. the number of times each number in 2) above occurs where the position of these numbers indicate the rows which satisfy all the 3 conditions of – if ANY column B, D or F has “Y”, if NONE of the columns C, E & G contain “Out” & Dates in column H are between I2 & I3. The numbers represent the number of times the corresponding column A value repeats for the rows which satisfy all the 3 conditions. For example, the 1st number 2 in the 1st position indicates that the number 1 (1st position in bins_array argument which is cell A2) appears twice in the data_array argument of 2) above (cell A2 value of “A” appears once in the 1st position of A2 and then again in the 5th position ie. in cell A6 where corresponding column values (B2:H2 & B6:H6) satisfy all 3 conditions. Similarly, the number 2 in the 4th position indicates that the number 4 (4th position in bins_array argument which is cell A5) appears twice in the data_array argument of 2) above (cell A5 value of “A” appears once in the 4th position of A5 and then again in the 6th position ie. in cell A7 where corresponding column values (B5:H5 & B7:H7) satisfy all 3 conditions. As explained, the Frequency of the bins_array argument number is returned only once at the position of its 1st occurrence.
5) IF(FREQUENCY(IF(( B2:B20=”Y”)+ (D2:D20=”Y”)+ (F2:F20=”Y”), IF((C2:C20<> “Out”)*( E2:E20<> “Out”)*( G2:G20<> “Out”), IF(( H2:H20 >= I2)*( H2:H20<= I3), IF(A2:A20<>””, MATCH( A2:A20, A2:A20,0))))), ROW(A2:A20)- ROW(A2)+1), 1): returns an array of 1s and FALSE values – {1;FALSE; FALSE;1; FALSE; FALSE; FALSE; FALSE;…} – the non-blank cells in column A which satisfy all the 3 conditions, 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.
———————————————————————————————–
Related Formulas – illustrated in the Ebook of Excel Formulas.
Cell J4 Formula: Counts Unique values in column A subject to multiple AND/OR conditions: (i) If ANY column B, D or F has “Y” (if atleast one of these columns contains “Y”); (ii) If NONE of the columns C, E & G contain “Out” (not even one of these 3 columns should have “Out”); (iii) Dates in column H are between I2 (Start Date) & I3 (End Date). This formula checks multiple columns as a combo, and is ideal where a large number of columns are to be validated because checking each column separately may make the formula unwieldy and complex.