Count matching text in two columns

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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!
 
=SUMPRODUCT(--(C4:C1000=D4:D1000))

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)
 
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?
 
Well, you were right, I assume the OP had the whole lot in the cells not
single entries
 
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)
 
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)
 
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)
 
Back
Top