[solved]Count if cell from one column is equal to cell in other column

Hello. I need a little help. It's been bothering me for hours now and I'm out of patience. I need to count how many times Number in Column A is equal to number in Column B. So A1=B1, A2=B2, A3=B3 and so on... I need answer in one cell (I could do it if I would use one more column for example in C1 I would write IF(A1=B1;1;0), C2 IF(A2=B2;1;0) and so on..., but I need more space efficient way now), I tried using COUNTIF(A:A;"="&B:B), COUNTIF(A:A;"=B:B), COUNTIF(A:A;B:B) and so on.. (I use A:A, B:B..., because it's quicker), but it doesn't give me the right number (actually, I get different numbers if I copy the same formula one row lower)..

Originally Posted by José Augusto

Try
=SUMPRODUCT((A:A=B:B)*(A:A<>""))

You should avoid using entire columns as range references with the SUMPRODUCT function. Use small specific ranges.

Originally Posted by Tony Valko

You should avoid using entire columns as range references with the SUMPRODUCT function. Use small specific ranges.

Well.. It didn't take a long time to count (if that's the problem with it)..

If you're satisfied with the performance that's all that counts!

Just did a quick test in Excel 2010.

Average calc times for these 2 formulas:

=SUMPRODUCT((A1:A100=B1:B100)*(A1:A100<>""))

=SUMPRODUCT((A:A=B:B)*(A:A<>""))

Did 5 calculations and averaged those results.

Formula1: 0.000842
Formula2: 0.121852

Time is in seconds

As you can see Formula1 is significantly faster to calculate compared to Formula2.

You're welcome. Thanks for the feedback!