**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 & B**. **Unique 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 A**. **Consider 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).**

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