User Rating: 5 / 5

Star ActiveStar ActiveStar ActiveStar ActiveStar Active
 

 

Count Multiple Occurrences of Array Values in a Range

 

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 Multiple Occurrences of Array Values in a Range; Count Duplicates in 2 Columns

 

 

Column E formulas: (i) Count Occurrences of multiple Column B values in Column A; (ii) Count Unique values Common in both columns A & B; (iii) Return MAX corresponding Number from Column C where a column B value occurs more than once in column A.

 

Cell E4 formula: Counts Occurrences of column B values in column A (Duplicates in column B are counted as many times as each appears in column A) - ex. "Value3" appearing twice in column B is considered twice and if it occurs twice in column A then it is counted as 4 (2 values of column B appearing 2 times each in column A).

 

Cell E11 formula: Counts Occurrences of column B values in column A (Unique column B values are counted as many times each appears in column A). Unique column B values are counted as many times as each appears in column A - ex. "Value3" appearing twice in column B is considered only once and if it occurs twice in column A then it is counted as 2 (1 value of column B appearing 2 times in column A).

 

Cell E23 formula: Count of Unique values Common in both columns A & BUnique column B values which appear in column A (multiple appearances in column A is considered only once), are counted.

 

Cell E42 / E50 formulas: Return MAX corresponding Number from Column C where a column B value occurs more than once in column AConsider only those column B values which occur multiple times in column A, and return the MAX corresponding value from column C.

 

 

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

 

Cell E11 formula Counts Occurrences of column B values in column A (Unique column B values are counted as many times each appears in column A). Unique column B values are counted as many times as each appears in column A - ex. "Value3" appearing twice in column B is considered only once and if it occurs twice in column A then it is counted as 2 (1 value of column B appearing 2 times in column A).

 

 

Count Occurrences of Unique Values 

     

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

 

 

Cell E11 formula: 

 

 

=SUMPRODUCT(--ISNUMBER( MATCH( A2:A20, B2:B20, 0)))  

 

 

Explanation - Cell E11 formula (Non-Array Formula):

 

1) MATCH(A2:A20, B2:B20, 0): returns an array of numbers and error values - {1;#N/A; 3;1; #N/A;3; 1;#N/A; #N/A;…} - if a column B value appears in column A, its column B position (or row number) will be repeated at each position it appears in column A. Example, the number 1 indicates that the 1st column B value (cell B2) appears in row positions 1, 4 & 7 in column A (ie. where 1 appears in the positions of cells A2, A5 & A8). Similarly the number 3 indicates that the 3rd value (cell B4) appears in positions 3 & 6 in column A (ie. in cells A4 & A7). Note: only the 1st instance of a value in column B (in case it appears multiple times in column B) is considered in MATCH. Using ISNUMBER will return TRUE for each number in the array, and using double negation converts TRUE to 1 and FALSE to zero, and then using SUMPRODUCT will return the Total number of times column B values occur in column A. 

 

 

Cell E4 Formula: Counts Occurrences of column B values in column A (Duplicates in column B are counted as many times as each appears in column A). Ex. "Value3" appearing twice in column B is considered twice and if it occurs twice in column A then it is counted as 4 (2 values of column B appearing 2 times each in column A). Non-array formula.

 

Cell E23 Formula: Count of Unique values Common in both columns A & B. Unique column B values which appear in column A (multiple appearances in column A is considered only once), are counted. Counts the number of duplicate values in columns A & B, ie. unique values which occur in both columns A & B - ex. there are 2 values ("Value1" and "Value3" which occur in both columns either once or multiple times. Array formula (Ctrl+ Shift+ Enter).

  

Cell E42 / E50 Formulas: Return MAX corresponding Number from Column C where a column B value occurs more than once in column A. Consider only those column B values which occur multiple times in column A, and return the MAX corresponding value from column C. Cell E42 is a Non-array formula, Cell E50 is an Array Formula (Ctrl+ Shift+ Enter).

 

 

 

    

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