Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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! |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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! |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Help with Matching Text Fields - Then Moving the Matching Cells Side by Side | Excel Discussion (Misc queries) | |||
matching columns using text strings! | Excel Worksheet Functions | |||
Matching 2 text columns | Excel Discussion (Misc queries) | |||
Compare cells/columns and highlight matching text strings | Excel Worksheet Functions | |||
How to count matching text | Excel Discussion (Misc queries) |