< Go Back

Count matches between two columns

Formula

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.

Example

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:

=SUMPRODUCT(--- (B5:B14=D5:D10))

Figure 1: Count matches of name records between columns in Excel.

Notes

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:

=SUMPRODUCT(--(B5:B14<>D5:D10))

Our customers love us!
“The expert was absolutely amazing and stuck with me the whole way through. They were polite, patient, seemed to want to genuinely help me and provided a solution that I would never have managed otherwise. I could not be more thankful for their support and solution. Thank you!” - - Chris T, in California
This blog is one way to figure it out. A 20 minute chat session with an expert is a better way.Get step-by-step guidance on your question

Leave a Comment

avatar