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.

up vote 4 down vote accepted

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.

In Excel you can use SUMPRODUCT function in a similar way to Andi's solution, but avoiding "array entry":

=SUMPRODUCT(0+(A1:A5=B1:B5))

that will also count any blank rows (as does Andi's) so if you want to avoid that change to this version:

=SUMPRODUCT((A1:A5<>"")*(A1:A5=B1:B5))

I would make it simpler with an IF Else statement so it could apply to numbers and characters

In C1, add the following

=IF(A1=B1, 1, 0)

Then, drag the formula down the length of the table and underneath, SUM the entire Cell

 =SUM(C1:C10)   // enter correct row numbers!

If you can use another column to store partial result, you can follow this way:

Column A | Column B | Column C (equal condition)
  1      |   4      | =IF(A1=B1;1;0)
  2      |   2      | =IF(A2=B2;1;0)
  3      |   5      | =IF(A3=B3;1;0)
  3      |   3      | =IF(A4=B4;1;0)
  5      |   3      | =IF(A5=B5;1;0)

In the cell C6 you write this formula =SUM(C1:C5)

Your Answer

 

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.