count number of cell have the same value

[duongthaiha]

Hi

I have two column
A B
-----------
1 2
2 2
3 1
4 3
5 4

I want to count the number of cell in column A that have the same value of cell at the same row of column B
In this example it should return 1 as A2=B2

Can you please show me how to do it plz?

Thanks you very much

 

[xenou]

I think this would work (Changing the cell references to the correct cells as needed):
=CountIf(B1:B5,A1:A5)

 

[duongthaiha]

I tried this and it not work.

 

[Alee Aasim]

type the formula in C2


=if(A2=B2,1,"")


Regs,
Aasim

 

[Brightspark98]

Try adding function in next cell (C1) =DELTA(A1,B1) and add sum function to bottom of sheet to count number of matches.

 

[sailepaty]

Try this,

=SUMPRODUCT((A1:A5=B1:B5)*1)

Regards

 

[Brightspark98]

Nice!

 

[duongthaiha]

it works thanks a lot :D

 

[duongthaiha]

Try this,

=SUMPRODUCT((A1:A5=B1:B5)*1)

Regards

thanks a lot for it :D