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”.
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”.