count if the two cells are equal
 

What do I use if I have two collums of data and I want to count how many of
them are the same
ie - A1 has a value of 1.1 while B1 has a value of 1.1
A2 has a value of 1.2 while B2 has a value of 1.4
I want the function to tell me that out of those two - one of those valus
remain the same in the next

count if the two cells are equal
 

One way:
=SUMPRODUCT(--(A1:A10=B1:B10))
Adjust ranges to the real size of columns!

Regards,
Stefi

€žsdwilson€� ezt Ã*rta:

What do I use if I have two collums of data and I want to count how many of
them are the same
ie - A1 has a value of 1.1 while B1 has a value of 1.1
A2 has a value of 1.2 while B2 has a value of 1.4
I want the function to tell me that out of those two - one of those valus
remain the same in the next

count if the two cells are equal
 

=SUMPRODUCT(--(A1:A20=B1:B20))
but if you want to omit rows where both are blank, try
=SUMPRODUCT(--(A1:A20=B1:B20),--(A1:A20<"")) or
=SUMPRODUCT((A1:A20=B1:B20)*(A1:A20<""))
--
David Biddulph

"sdwilson" wrote in message
...
What do I use if I have two collums of data and I want to count how many
of
them are the same
ie - A1 has a value of 1.1 while B1 has a value of 1.1
A2 has a value of 1.2 while B2 has a value of 1.4
I want the function to tell me that out of those two - one of those valus
remain the same in the next

count if the two cells are equal
 

Thanks! That worked but now i have another problem!
I am using the same formula but rather then =
When I add information, the formula adjusts however if I take that
information away, the formula doesn't change! THis also happens with COUNTA
function as well. Any ideas???


"David Biddulph" wrote:

=SUMPRODUCT(--(A1:A20=B1:B20))
but if you want to omit rows where both are blank, try
=SUMPRODUCT(--(A1:A20=B1:B20),--(A1:A20<"")) or
=SUMPRODUCT((A1:A20=B1:B20)*(A1:A20<""))
--
David Biddulph

"sdwilson" wrote in message
...
What do I use if I have two collums of data and I want to count how many
of
them are the same
ie - A1 has a value of 1.1 while B1 has a value of 1.1
A2 has a value of 1.2 while B2 has a value of 1.4
I want the function to tell me that out of those two - one of those valus
remain the same in the next

count if the two cells are equal
 

Make sure you're in automatic calculation mode.

In xl2003:
tools|options|calculation tab

sdwilson wrote:

Thanks! That worked but now i have another problem!
I am using the same formula but rather then =
When I add information, the formula adjusts however if I take that
information away, the formula doesn't change! THis also happens with COUNTA
function as well. Any ideas???

"David Biddulph" wrote:

=SUMPRODUCT(--(A1:A20=B1:B20))
but if you want to omit rows where both are blank, try
=SUMPRODUCT(--(A1:A20=B1:B20),--(A1:A20<"")) or
=SUMPRODUCT((A1:A20=B1:B20)*(A1:A20<""))
--
David Biddulph

"sdwilson" wrote in message
...
What do I use if I have two collums of data and I want to count how many
of
them are the same
ie - A1 has a value of 1.1 while B1 has a value of 1.1
A2 has a value of 1.2 while B2 has a value of 1.4
I want the function to tell me that out of those two - one of those valus
remain the same in the next




--

Dave Peterson