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

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.

``````{=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

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