Count Occurrences in one or both Columns, consider Once if Occurrence in Same Row of 2 columns
Count Occurrences of a Value in Whole or in Part: (i) Count Once if Occurrence in Same Row of One or Both columns or (ii) Count Twice if Occurrence in Both Columns
Column E COUNTS Occurrences where (column A = “Yes”) AND (Either or Both Column B & Column C contain cell D5 value in whole or in part).
Cell E5 formula: Count TWICE if both column B & C cells in the same row contain cell D5 value in whole or in part. If cell D5 value occurs multiple times in a cell consider & count only once.
———————————————————————————————–
Cell E5 formula Counts TWICE if both column B & C cells in the same row contain cell D5 value in whole or in part, and where column A = “Yes”).
Cell E5 formula:
If cell D5 value occurs multiple times in a cell consider & count only once.
=SUMPRODUCT(( A2:A20= “Yes”)*ISNUMBER( SEARCH(D5, B2:C20)))
Explanation – Cell E5 formula (Non-Array Formula):
1) ISNUMBER( SEARCH(D5, B2:C20)): returns an array of TRUE and FALSE values comprising of 2 columns and 19 rows – {TRUE, FALSE; TRUE, TRUE; FALSE, FALSE; FALSE, FALSE; TRUE, FALSE; TRUE, FALSE;…} – each row has 2 values separated with a comma, one for each of the 2 columns. TRUE indicates where D5 value occurs in a cell in whole or as part of text.
2) (A2:A20= “Yes”)*ISNUMBER( SEARCH(D5, B2:C20)): returns an array of 1s and zeros comprising of 2 columns and 19 rows – {1,0; 0,0; 0,0; 0,0; 0,0; 1,0; 0,0; 0,1;0,0; 0,0; 0,0; 1,1; 0,0; 1,1; 0,0; 0,0; 0,0; 0,0; 0,0} – the 1s indicate the cells where column A = “Yes” AND column B / C contain cell D5 value in whole or as part of text. Where CELL D5 value is present in both columns B & C, 1 will appear as a pair – “1,1”. Using SUMPRODUCT will return the COUNT of occurrences of cell D5 value in columns B & C where corresponding column A value is “Yes, and will count twice if cell D5 value is present in the same row of both columns B & C.
———————————————————————————————–
Related Formulas – illustrated in the Ebook of Excel Formulas.
COUNT Occurrences where (column A = “Yes”) AND (Either or Both Column B & Column C contain cell D5 value in whole or in part) – options of (i) Count Once if Occurrence in Same Row of One or Both columns or (ii) Count Twice if Occurrence in Both Columns
Cell E6 Formula: Similar to cell E5 formula – Count TWICE if both column B & C cells in the same row contain cell D5 value in whole or in part, and where column A = “Yes”. If cell D5 value occurs multiple times in a cell consider & count only once. Uses SUMPRODUCT and ISNUMBER with SEARCH – NonArray formula.
Cell E7 Formula: Similar to cell E5 formula – Count TWICE if both column B & C cells in the same row contain cell D5 value in whole or in part, and where column A = “Yes”. If cell D5 value occurs multiple times in a cell consider & count only once. Uses SUM, IF condition and ISNUMBER with SEARCH – Array formula (Ctrl+Shift+ Enter).
Cell E10 Formula: Count TWICE if both column B & C cells in the same row contain cell D5 value in whole or in part, and where column A = “Yes”. If cell D5 value occurs multiple times in a cell count as many times it appears. Uses SUM, IF condition, LEN and SUBSTITUTE – Array formula (Ctrl+Shift+ Enter).
Cell E13 Formula: Count ONLY ONCE if both column B and column C cells in the same row contain cell D5 value in whole or as part of text, and where column A = “Yes”. Uses FREQUENCY and ISNUMBER with SEARCH – Array formula (Ctrl+Shift+Enter).
Cell E14 Formula: Count ONLY ONCE if both column B and column C cells in the same row contain cell D5 value in whole or as part of text, and where column A = “Yes”. Uses MMULT and ISNUMBER with SEARCH – Array formula (Ctrl+Shift+ Enter).
Cell E15 Formula: Count ONLY ONCE if both column B and column C cells in the same row contain cell D5 value in whole or as part of text, and where column A = “Yes”. Uses MMULT and ISNUMBER with SEARCH (hardcodes the array2 argument of MMULT) – NonArray formula.
Cell E16 Formula: Count ONLY ONCE if both column B and column C cells in the same row contain cell D5 value in whole or as part of text, and where column A = “Yes”. Uses SUMPRODUCT and ISNUMBER with SEARCH twice – NonArray formula.