=sum(if(a1=b1,1,0),if(a2=b2,1,0),if(a3=b3,1,0),.....)

[Farkas Attila]

I have 2 columns
A B
1 a a
2 d d
3 c a
I want to know how many lines A=B -this must be an numerical value -in this
case 2
=sum(if(a1=b1,1,0),if(a2=b2,1,0),if(a3=b3,1,0),.....) is a solution
But simply is more usefully
Anyone can help me pls?

 

[Pete_UK]

Try this array* formula:

=SUM(IF(A1:A10=B1:B10,1,0))

Change the ranges to suit.

* An array fromula has to be committed using <CTRL><SHIFT><ENTER>
(CSE) instead of the usual <ENTER>. If you do this correctly then
Excel will wrap curly braces { } around the formula when viewed in the
formula bar - you must not type these yourself. If you amend the
formula, then use CSE again to commit it.

Hope this helps.

Pete

 

[Mike H]

Hi,

Change the range to suit

=SUMPRODUCT((A1:A20>"")*(A1:A20=B1:B20))

Mike