Use the SUMPRODUCT function to compare two columns and count matches in the corresponding rows.T he generic formula is denoted as:
=SUMPRODUCT(-- -- (range1=range 2))
How the SUMPRODUCT function works
The SUMPRODUCT function is a flexible formula that handles selections without paying any special attention to the array syntax. However, it instead multiplies and subsequently sums the product of the selected arrays. The flexibility of the function is due to the fact that other functions can be easily incorporated into the SUMPRODUCT function to widen its functionality.
To further illustrate how the SUMPRODUCT formula function operates, here is an example of how to compare two columns and count matches in corresponding rows using the SUMPRODUCT function in excel
In the example shown below, we have two columns with some records of names. Suppose I am interested in comparing the two records and subsequently count the number of names that appear in both the first and second column. All I have to do is simply use the SUMPRODUCT formula. In the provided screenshot, the formula used is captured as:
Figure 1: Count matches of name records between columns in Excel.
In the above formula are the two columns that I need to count the number of duplicate records. So, you can customize the cell references to suit your needs.
How to count non –matching rows
Now for us to count the not -matching names in the above examples.W e reverse the captured formula to appear as follows: