User Rating: 5 / 5

Star ActiveStar ActiveStar ActiveStar ActiveStar Active
 

 

Count Values Once (Unique) or as many times they Occur, Condition in Either 1 or Both Columns

 

 

Count Values where Multiple Occurrences are Counted Once (Unique values) for Condition of "Yes" in Either 1 or Both Columns 

 

Cell E4: Count number of Unique Names in column A (Multiple Occurrences are Counted Once), if Either or Both Column B & C have "Yes".

  

-----------------------------------------------------------------------------------------------

 

Cell E4 formula Counts number of Unique Names in column A (Multiple Occurrences are Counted Once), if Either or Both Column B & C have "Yes".

 

Count Uniques, Condition in 1 or Both Columns  

 

Cell E4 formula: 

 

Returns 4 for the Unique Names "John", "Stacey", "Mary" & "White". 

 

=SUM(IF( FREQUENCY(IF(( B2:C20="Yes"), IF(A2:A20<>"", MATCH(A2:A20, A2:A20,0))), ROW(A2:A20)- ROW(A2)+1), 1))   

 

Explanation - Cell E4 formula (Array Formula - Ctrl+ Shift+ Enter):

 

1) IF((B2:C20= "Yes"), IF(A2:A20<>"", MATCH(A2:A20, A2:A20, 0))): returns an array of numbers and FALSE values comprising of 2 columns and 19 rows - {1,FALSE; 2,2; FALSE,3; FALSE, FALSE; FALSE, FALSE;1,1; FALSE, FALSE; FALSE, FALSE; 8,FALSE;...} - the numbers indicate the relative position of each column A value (where either or both column B & C = "Yes") with similar values having the same position. Blank cells, or where there is no "Yes" in even one of the 2 columns (column B & C), return FALSE. Where "Yes" is present in both columns B & C, the number will appear as a pair, viz "2,2". For example, the 1st number 1 indicates the name occurs the 1st position of cell A2 and only column B = "Yes", the next number 2 indicates the name in the 2nd position of cell A3 and both column B & C = "Yes" (number 2 occurs as "2,2"), the next number 3 indicates the name in the 3rd position of cell A4 and and only column C = "Yes", then the number 1 is repeated again in the 6th position but because it already appears in the 1st position of cell A2 it apepars as 1 and both column B & C = "Yes" (number 1 occurs as "1,1"), and so on. This is the data_array argument of the FREQUENCY function.

 

2) ROW(A2:A20)- ROW(A2)+1: This returns an array of values 1 to 19 - {1;2; 3;4;5; 6;7;8; 9;10;…} - against which the Frequency of each data_array value in 1) above is determined. 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( IF((B2:C20= "Yes"), IF(A2:A20<>"", MATCH(A2:A20, A2:A20, 0))), ROW(A2:A20)- ROW(A2)+1): returns an array of numbers and zeros - {3;2; 1;0; 0;0; 0;1; 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 1) above occurs (these are the frequency of row numbers / positions of non-blank column A cells where either or both column A & B = "Yes"). For example, the 1st number 3 in the 1st position indicates that the number 1 (1st position in bins_array argument) appears thrice in the data_array argument of 1) above (it appears thrice because for cell A2 the value in cell B2 = "Yes" & for cell A7 where A2 name appears again the value of both cells B7 & C7 = "Yes" and each "Yes" is counted as 1). Similarly, the number 2 in the 2nd position indicates that the number 2 (2nd position in bins_array argument) is appears twice in the data_array argument of 1) above (it appears twice because for cell A3 value both cells B3 & C3 = "Yes"), and so on. As explained, the Frequency of the bins_array argument number is returned only once at the position of its 1st occurrence.

 

4) IF(FREQUENCY(IF(( B2:C20="Yes"), 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;1;1; FALSE; FALSE; FALSE; FALSE; 1;FALSE; FALSE;...} - the non-blank cells in column A where either or both column B & C = "Yes" 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.  

 

 

Cells E8:E10 and Cell E12 formulas Count number of Names in column A including Duplicates (Multiple Occurrences are Counted as many times they Occur), if Either or Both Column B & C have "Yes"

 

Cell E8 Formula: Array formula (Ctrl+Shift+Enter) using Frequency. Returns 5 for the Names "John", "Stacey", "Mary", "John" & "White".

 

Cell E9 Formula: Array formula (Ctrl+Shift+Enter) using MMULT. Returns 5 for the Names "John", "Stacey", "Mary", "John" & "White".

 

Cell E10 Formula: Non-Array formula using MMULT - hard codes the number of columns (array2 of MMULT) which consider "Yes". If 3 columns are to be considered instead of 2 (ie. B:D instead of B:C, the formula will be: =SUMPRODUCT(1*( MMULT(1*( B2:D20= "yes"), {1;1;1}) >0)). For 2 columns ROW(1:2) can be used instead of {1;1}, & for 3 columns ROW(1:3) can be used instead of {1;1;1}. Returns 5 for the Names "John", "Stacey", "Mary", "John" & "White".

 

Cell E12 Formula: Non-Array formula using SUMPRODUCT. Returns 5 for the Names "John", "Stacey", "Mary", "John" & "White".

 

Note: Formulas in cells E8:E10 are amenable to check "Yes" in multiple columns in one go ("B2:C20" range can be expanded to include multiple columns) whereas cell E12 formula considers each column separately to check "Yes" ie. "B2:B20" & "C2:C20".