# Count Total Number of Matching Cells Across Two Columns

1. ## Count Total Number of Matching Cells Across Two Columns

Greetings,

I work in a call center and work with large spread sheets that sometimes contain more than 25,000 rows of information regarding individual phone interactions with clients. One column contains a time stamp from when a client's profile was created. The adjacent column contains a time stamp for the individual conversation. If the information in two adjacent cells matches, that indicates that the conversation was with a first-time caller. I need a formula that will count the number of times two adjacent cells within these two columns match one another.

If at all possible, Id like to avoid an approach that requires creating another column, inserting a formula and dragging down to the final row. In the example workbook Ive posted, there are 9 rows whose data is matching. A formula that could be placed in a single cell and calculate those nine instances would be excellent.

Thanks so much for your help.  Register To Reply

2. ## Re: Count Total Number of Matching Cells Across Two Columns

Hello, Try any of these,

=SUMPRODUCT(--(A2:A14=B2:B14))

=SUMPRODUCT(--ISNUMBER(MATCH(\$A\$2:\$A\$14,\$B\$2:\$B\$14,0)))

If you have huge data, this may slow the calculation.  Register To Reply

3. ## Re: Count Total Number of Matching Cells Across Two Columns

Hi Haseeb. Thanks for the quick reply!

The first formula worked well for this specific sheet but it occurred to me that I left out an important piece of information in my explanation of what I'm looking for. I'm creating a template for analyzing worksheets like the one I posted and the number of rows in those worksheets varies from a few hundred to tens of thousands. It would be ideal if the formula could calculate the matching cells for the entire column without having to annotate the range of cells (In this case A2:A14 and B2:B14). This would allow me to plug in my spreadsheets (huge or small) and have the numbers calculated without having to make any modifications to the formula.

Thanks for your help and sorry for the miscommunication.  Register To Reply

4. ## Re: Count Total Number of Matching Cells Across Two Columns

The easiest way is to just pick a row number that you never think you'll exceed, e.g. A2:A20000 and B2:B20000. I believe in Excel 2007+ you can reference whole columns in SUMPRODUCT formulas (in Excel 2003- you can't), but be advised that this could greatly impact performance.  Register To Reply

5. ## Re: Count Total Number of Matching Cells Across Two Columns

Or define a dynamic named range & use it in SUMPRODUCT  Register To Reply