Table of Contents
Counting the number of matches between two columns in Excel can be achieved by using the COUNTIF function. This function allows you to specify a range of cells to search and a criteria to match against. By selecting the two columns as the range and setting the criteria to match the values in one column against the values in the other column, the function will return the total number of matches. This provides a simple and efficient way to accurately count the number of matches between two columns in Excel.
Excel: Count Number of Matches Between Two Columns
You can use the following syntax to count the number of matches between two columns in Excel:
=SUMPRODUCT(--(A2:A11=B2:B11))
This particular formula counts the number of matches between the range A2:A11 and the range B2:B11.
You can also use the following formula to count the number of non-matches between two columns:
=SUMPRODUCT(--(A2:A11<>B2:B11))
Note: The symbols <> stand for “not equal” in Excel.
The following example shows how to use these formulas in practice.
Example: Count Number of Matches Between Two Columns in Excel
Suppose we have the following two columns in Excel that contain the names of various basketball teams:
We can type the following formula into cell D2 to count the number of matching team names between columns A and B:
=SUMPRODUCT(--(A2:A11=B2:B11))
The following screenshot shows how to use this formula in practice:
The formula returns a value of 4.
We can manually confirm that this is correct by identifying each of the matching team names between the two columns:
=SUMPRODUCT(--(A2:A11<>B2:B11))
The following screenshot shows how to use this formula in practice:
We can see that there are 6 teams that have non-matching names between the two columns.
The following tutorials explain how to perform other common operations in Excel:
Cite this article
stats writer (2024). How can I count the number of matches between two columns in Excel?. PSYCHOLOGICAL SCALES. Retrieved from https://scales.arabpsychology.com/stats/how-can-i-count-the-number-of-matches-between-two-columns-in-excel/
stats writer. "How can I count the number of matches between two columns in Excel?." PSYCHOLOGICAL SCALES, 22 Jun. 2024, https://scales.arabpsychology.com/stats/how-can-i-count-the-number-of-matches-between-two-columns-in-excel/.
stats writer. "How can I count the number of matches between two columns in Excel?." PSYCHOLOGICAL SCALES, 2024. https://scales.arabpsychology.com/stats/how-can-i-count-the-number-of-matches-between-two-columns-in-excel/.
stats writer (2024) 'How can I count the number of matches between two columns in Excel?', PSYCHOLOGICAL SCALES. Available at: https://scales.arabpsychology.com/stats/how-can-i-count-the-number-of-matches-between-two-columns-in-excel/.
[1] stats writer, "How can I count the number of matches between two columns in Excel?," PSYCHOLOGICAL SCALES, vol. X, no. Y, ص Z-Z, June, 2024.
stats writer. How can I count the number of matches between two columns in Excel?. PSYCHOLOGICAL SCALES. 2024;vol(issue):pages.