Excel - Summary count of multi-column duplicates

Question

I can't seem to wrap my head around an answer for this. I can find duplicates when using a formula that looks at a single sell and compare it to a range of cells, and copy that formula down every row; afterward I can just sum the results. However, I want a single formula in a single cell.

I want to take a range of cells in one column, compare that entire set to a range of cells in another column, and sum all duplicate cells where the cell in column 1 matches a cell in column 2. For this exercise, all cells in each column are unique within that column, they are only potentially duplicate between columns.

For example:

    C1 C2
R1   1  2
R2   2  6
R3   3  7
R4   4  1
R5   5  8

I want a single column sum with a result of 2 (the duplicate cells in C1 is 1 and 2).

This will ultimately be converted into a VBA script however I can work with starting with a single formula. If starting out at VBA is easier then that works for me too.

My specific question is: which functions in excel do I use to accomplish this task?

Thanks for your help.

Answer 1

I believe the formula you want in cell C3 is:

=IF(ISERROR(VLOOKUP(A1,$B$1:$B$5,1,FALSE)),0,1)

This will return a 1 if your column-1 value appears in column 2, and a 0 if it doesn't. Then you can simply copy it down for the extent of your data (changing the referenced range as needed) sum the results of column 3. (Note that the FALSE is important as it forces exact matches; otherwise, Excel will display approximate matches, which will compromise the accuracy of your results.)