this should be an easy one but i can't seem to figure it
out.....i'm comparing data in two columns, i would like to
count the number of cells in "b" that have the same data
as "a" ie: if a4 contains "john" and b4 contains "john"
then i want to count b4....etc..

thanks

Ctrl/shift/enter:
=SUM(1*NOT(ISNA(MATCH(A1:A100,B1:B100,0))))
Bob Umlas
ExcelMVP

"johnT" wrote in message
...
this should be an easy one but i can't seem to figure it
out.....i'm comparing data in two columns, i would like to
count the number of cells in "b" that have the same data
as "a" ie: if a4 contains "john" and b4 contains "john"
then i want to count b4....etc..

thanks

Another way

=SUMPRODUCT(--(COUNTIF(A1:A100,B1:B100)))

--
Regards,

Peo Sjoblom

(No private emails please, for everyone's
benefit keep the discussion in the newsgroup/forum)



"johnT" wrote in message
...
this should be an easy one but i can't seem to figure it
out.....i'm comparing data in two columns, i would like to
count the number of cells in "b" that have the same data
as "a" ie: if a4 contains "john" and b4 contains "john"
then i want to count b4....etc..

thanks

Hi
if they have to be in the same row try:
=SUMPRODUCT(--(A1:A100=B1:B10))

--
Regards
Frank Kabel
Frankfurt, Germany
"johnT" schrieb im Newsbeitrag
...
this should be an easy one but i can't seem to figure it
out.....i'm comparing data in two columns, i would like to
count the number of cells in "b" that have the same data
as "a" ie: if a4 contains "john" and b4 contains "john"
then i want to count b4....etc..

thanks