Skip to content
Free Excel Tutorials
  • Home
  • Excel For Beginners
  • Excel Intermediate
  • Advanced Excel For Experts

Data Analysis

  • How to create Checklist in Excel
  • Conflicting Multiple Conditional Formatting Rules in Excel
  • How To Filter Data in Excel
  • How to create running total in an Excel Table
  • How to count table rows in Excel

References

  • Lookup entire row in Excel
  • Last row number in range
  • Two-way lookup with VLOOKUP in Excel
  • CHOOSE function: Description, Usage, Syntax, Examples and Explanation
  • Convert text string to valid reference in Excel using Indirect function

Data Validations

  • Excel Data validation number multiple 100
  • Prevent invalid data entering in specific cells
  • Excel Data validation must begin with
  • Excel Data validation specific characters only
  • Excel Data validation date in specific year

Count matches between two columns in Excel

by

This tutorial shows how to Count matches between two columns in Excel using the example below;

Formula

=SUMPRODUCT(--(range1=range2))

Explanation

If you want to compare two columns and count matches in corresponding rows, you can use the SUMPRODUCT function with a simple comparison of the two ranges. For example, if you have values in B5:B11 and C5:C11 and you want to count any differences, you can use this formula:

=SUMPRODUCT(--(B5:B11=C5:C11))

How this formula works

The SUMPRODUCT function is a versatile function that handles arrays natively without any special array syntax. Its behavior is simple: it multiplies, then sums the product of arrays. In the example shown, the expression B5:B11 = C5:C11 will generate an array that contains TRUE and FALSE values like this:

{TRUE;TRUE;FALSE;TRUE;FALSE;FALSE;FALSE}

Note that we have 3 TRUE values because there are 3 matches.

In this state, SUMPRODUCT will actually return zero because TRUE and FALSE values are not counted as numbers in Excel by default. To get SUMPRODUCT to treat TRUE as 1 and FALSE as zero, we need to “coerce” them into numbers. The double negative is a simple way to do that:

--(B5:B11=C5:C11)

After coercion, we have:

{1;1;0;1;0;0;0}

With no other arrays to multiply, SUMPRODUCT simply sums the values and returns 3.

Count non-matching rows

To count non-matching values, you can reverse the logic like so:

=SUMPRODUCT(--(B5:B11<>C5:C11))

Post navigation

Previous Post:

How to use Excel CHOOSE Function

Next Post:

Customize Ribbon In Excel

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Learn Basic Excel

Ribbon
Workbook
Worksheets
Format Cells
Find & Select
Sort & Filter
Templates
Print
Share
Protect
Keyboard Shortcuts

Categories

  • Charts
  • Data Analysis
  • Data Validation
  • Excel Functions
    • Cube Functions
    • Database Functions
    • Date and Time Functions
    • Engineering Functions
    • Financial Functions
    • Information Functions
    • Logical Functions
    • Lookup and Reference Functions
    • Math and Trig Functions
    • Statistical Functions
    • Text Functions
    • Web Functions
  • Excel VBA
  • Excel Video Tutorials
  • Formatting
  • Grouping
  • Others

Logical Functions

  • IFNA function: Description, Usage, Syntax, Examples and Explanation
  • XOR function: Description, Usage, Syntax, Examples and Explanation
  • OR function: Description, Usage, Syntax, Examples and Explanation
  • How to use Excel AND Function
  • Extract multiple matches into separate rows in Excel

Date Time

  • Calculate years between dates in Excel
  • Get fiscal quarter from date in Excel
  • Get days between dates ignoring years in Excel
  • HOUR function: Description, Usage, Syntax, Examples and Explanation
  • Calculate expiration date in Excel

Grouping

  • Group numbers at uneven intervals in Excel
  • Group times into unequal buckets in Excel
  • Categorize text with keywords in Excel
  • Map inputs to arbitrary values in Excel
  • How to randomly assign data to groups in Excel

General

  • How to calculate total from percentage in Excel
  • With vs Without Array Formula in Excel
  • Create dynamic workbook reference to another workbook in Excel
  • How to calculate percent variance in Excel
  • How to create dynamic named range with INDEX in Excel
© 2026 xlsoffice . All Right Reserved. | Teal Smiles | Abbreviations And Their Meaning