Count the number of rows with equal values in two specified columns
Solution 1
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.
Solution 2
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))
Solution 3
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!
Related videos on Youtube
John Manak
Updated on September 18, 2022Comments
-
John Manak over 1 year
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.