User Rating: 5 / 5

Star ActiveStar ActiveStar ActiveStar ActiveStar Active
 

 

Count Occurrences in one or both Columns, consider Once if Occurrence in Same Row of 2 columns

 

Related Links: 

1. Excel VLOOKP Function, with examples.

2. Left Lookup with VLookup Excel function.

3. Left Lookup, with Index, Match & Offset Excel functions.

4. Vlookup Multiple Values - Return MULTIPLE corresponding values for ONE Lookup Value.

 


 

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

 

 

Count Occurrences in 1 or both Columns

  

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