User Rating: 5 / 5     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).

Cells E5:E7: 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 E10: 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 count as many times it appears.

Cells E13:E16: Count ONLY ONCE if both column B and column C cells in the same row contain D5 value in whole or as part of text.

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

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").

"Open Image in New Tab" for a full and clear view.

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.

Cell E6 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: 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.

Only One of the Multiple formulas is illustrated above. For full details and explanation refer to Ebook of Excel Formulas.