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

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 :D
Anyone can help me pls?

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

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

On Apr 16, 12:52*pm, Farkas Attila <Farkas
wrote:
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 :D
Anyone can help me pls?

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

Hi,

Change the range to suit

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

Mike

"Farkas Attila" wrote:

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 :D
Anyone can help me pls?