2

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.

4

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.

| improve this answer | |
3

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))

| improve this answer | |
1

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!
| improve this answer | |
0

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)

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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