Count matching text in two columns
 

Didn't look at the data closely enough

=SUMPRODUCT(--(ISNUMBER(MATCH(C1:C10,D1:D10,0))))

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)



"SteveC" wrote in message
...
Thanks Peo and Bob,

But I noticed something... when I applied it to the my worksheet, it
didn't
return the number of times column C matched Column D.

When I added a helper column E with a vlookup formula, and subtracted the
number of #N/As from the original number of cells that contained text, I
got
a much different (and I think correct) number.

Helper column formula, filled down 1000 rows, starting in cell E4:
=VLOOKUP(C4,Ticker!$D$4:$D$1000,1,FALSE)

"Peo Sjoblom" wrote:

=SUMPRODUCT(--(C4:C1000=D4:D1000),--(C4:C1000<""))



--
Regards,

Peo Sjoblom



"SteveC" wrote in message
...
Thanks Bob. I got close on my own, but this will do it.

How do I modify it so it doesn't count blank cells?


"Bob Phillips" wrote:

=SUMPRODUCT(--(C4:C1000=D4:D1000))

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my
addy)



"SteveC" wrote in message
...
C4:C1000 contains text: a, b, d, e, f, h

D4:D1000 contains text: a, d, e, f, i, g

what formula would count the number of instances where the text in
one
column matches the other. In this example, the formula should
return 4
(a,
d, e, f match)

Thanks a lot!

Count matching text in two columns
 

That's it exactly. Thanks again and happy July4!

"Bob Phillips" wrote:

Didn't look at the data closely enough

=SUMPRODUCT(--(ISNUMBER(MATCH(C1:C10,D1:D10,0))))

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)



"SteveC" wrote in message
...
Thanks Peo and Bob,

But I noticed something... when I applied it to the my worksheet, it
didn't
return the number of times column C matched Column D.

When I added a helper column E with a vlookup formula, and subtracted the
number of #N/As from the original number of cells that contained text, I
got
a much different (and I think correct) number.

Helper column formula, filled down 1000 rows, starting in cell E4:
=VLOOKUP(C4,Ticker!$D$4:$D$1000,1,FALSE)

"Peo Sjoblom" wrote:

=SUMPRODUCT(--(C4:C1000=D4:D1000),--(C4:C1000<""))



--
Regards,

Peo Sjoblom



"SteveC" wrote in message
...
Thanks Bob. I got close on my own, but this will do it.

How do I modify it so it doesn't count blank cells?


"Bob Phillips" wrote:

=SUMPRODUCT(--(C4:C1000=D4:D1000))

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my
addy)



"SteveC" wrote in message
...
C4:C1000 contains text: a, b, d, e, f, h

D4:D1000 contains text: a, d, e, f, i, g

what formula would count the number of instances where the text in
one
column matches the other. In this example, the formula should
return 4
(a,
d, e, f match)

Thanks a lot!

Count matching text in two columns
 

Means nothing to me, I am UK based

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)



"SteveC" wrote in message
...
That's it exactly. Thanks again and happy July4!

"Bob Phillips" wrote:

Didn't look at the data closely enough

=SUMPRODUCT(--(ISNUMBER(MATCH(C1:C10,D1:D10,0))))

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my
addy)



"SteveC" wrote in message
...
Thanks Peo and Bob,

But I noticed something... when I applied it to the my worksheet, it
didn't
return the number of times column C matched Column D.

When I added a helper column E with a vlookup formula, and subtracted
the
number of #N/As from the original number of cells that contained text,
I
got
a much different (and I think correct) number.

Helper column formula, filled down 1000 rows, starting in cell E4:
=VLOOKUP(C4,Ticker!$D$4:$D$1000,1,FALSE)

"Peo Sjoblom" wrote:

=SUMPRODUCT(--(C4:C1000=D4:D1000),--(C4:C1000<""))



--
Regards,

Peo Sjoblom



"SteveC" wrote in message
...
Thanks Bob. I got close on my own, but this will do it.

How do I modify it so it doesn't count blank cells?


"Bob Phillips" wrote:

=SUMPRODUCT(--(C4:C1000=D4:D1000))

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in
my
addy)



"SteveC" wrote in message
...
C4:C1000 contains text: a, b, d, e, f, h

D4:D1000 contains text: a, d, e, f, i, g

what formula would count the number of instances where the text
in
one
column matches the other. In this example, the formula should
return 4
(a,
d, e, f match)

Thanks a lot!