Excel – Count the number of rows with equal values in two specified columns

google-spreadsheetslibreoffice-calcmicrosoft excelspreadsheet

I have two columns in MS Excel/LibreOffice Calc/Google Spreadsheets with numbers. I would like to count the number of rows which have identical values in both columns.

In the following example:

Column A | Column B
   1     |     4
   2     |     2
   3     |     5
   3     |     3
   5     |     3

there are two rows with equal numbers: rows 2 (2=2) and 4 (3=3). I was hoping for solutions like =SUM(A1:A5=B1:B5) to work; unfortunately, they don't.

UPDATE

I know the solution would easy if I were to create a third column in which I would compare each pair of values, row by row (as Dave and Joe suggested, using e.g. =IF(A1=B1, 1, 0)); however, I'm looking for a one-liner as I don't want to clutter the spreadsheet with intermediate results.

Best Answer

You're nearly right. You can adapt your formula slightly like this:

{=SUM(IF(A1:A5=B1:B5,1,0))}

Type this in without the curly {} brackets, and press Ctrl+Shift+Enter so that Excel knows it's an array formula. The curly brackets represent the fact it's an array formula.

Related Question