Count Multiple Occurrences of Array Values in a Range
Count Multiple Occurrences of Array Values in a Range
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 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 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.
—————————————————————————–
Related Formulas – illustrated in the Ebook of Excel Formulas.
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). 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).