Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Search for either one or the other
Hi,
I'm trying to count the number of entries in a range B2:B1847 that are either BKROCK BLACKRCK BLACKROCK BLACROCK BLK RCK BLKRCK BLKRK BLKRO BLKROCK (note that these all have numerous strings of differing amounts of numbers on the end so I cannot use a RIGHT function) The formula I had been using up until now was =SUMPRODUCT((#other criteria#)*(LEFT(B2:B1847,2)="BL")) but this returns BLACK RIVER which I don't want to count and doesn't include BKROCK OR BLKRO. I also tried the following: =SUMPRODUCT((#other criteria#)*(ISNUMBER(SEARCH("CK",B2:B1847)))) but this counts MAVERICK CAPITAL as well and not BLKRO. I wondered if I could perhaps combine a SEARCH and OR function as follows =SUMPRODUCT((#other criteria#)*(ISNUMBER(SEARCH(OR("BKROCK","BLACKRCK" ,"BLACKROCK","BLACROCK","BLK RCK","BLKRCK","BLKRK","BLKRO","BLKROCK" ),B2:B1847)))) but it just returns the value 0. Does anyone know what I'm doing wrong and if there is another way around it? Thanks! Last edited by blivy : August 7th 12 at 10:18 AM Reason: Realised there were extra Black Rock variations |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Search for either one or the other
On Tuesday, August 7, 2012 4:09:17 AM UTC-5, blivy wrote:
Hi, I'm trying to count the number of entries in a range B2:B1847 that are either BKROCK BLACKRCK BLACKROCK BLACROCK BLK RCK BLKRCK BLKRK BLKRO BLKROCK (note that these all have numerous strings of differing amounts of numbers on the end so I cannot use a RIGHT function) The formula I had been using up until now was =SUMPRODUCT((#other criteria#)*(LEFT(B2:B1847,2)="BL")) but this returns BLACK RIVER which I don't want to count and doesn't include BKROCK OR BLKRO. I also tried the following: =SUMPRODUCT((#other criteria#)*(ISNUMBER(SEARCH("CK",B2:B1847)))) but this counts MAVERICK CAPITAL as well and not BLKRO. I wondered if I could perhaps combine a SEARCH and OR function as follows =SUMPRODUCT((#other criteria#)*(ISNUMBER(SEARCH(OR("BKROCK","BLACKRCK" ,"BLACKROCK","BLACROCK","BLK RCK","BLKRCK","BLKRK","BLKRO","BLKROCK" ),B2:B1847)))) but it just returns the value 0. Does anyone know what I'm doing wrong and if there is another way around it? Thanks! -- blivy |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Search for either one or the other
On Tuesday, August 7, 2012 4:09:17 AM UTC-5, blivy wrote:
Hi, I'm trying to count the number of entries in a range B2:B1847 that are either BKROCK BLACKRCK BLACKROCK BLACROCK BLK RCK BLKRCK BLKRK BLKRO BLKROCK (note that these all have numerous strings of differing amounts of numbers on the end so I cannot use a RIGHT function) The formula I had been using up until now was =SUMPRODUCT((#other criteria#)*(LEFT(B2:B1847,2)="BL")) but this returns BLACK RIVER which I don't want to count and doesn't include BKROCK OR BLKRO. I also tried the following: =SUMPRODUCT((#other criteria#)*(ISNUMBER(SEARCH("CK",B2:B1847)))) but this counts MAVERICK CAPITAL as well and not BLKRO. I wondered if I could perhaps combine a SEARCH and OR function as follows =SUMPRODUCT((#other criteria#)*(ISNUMBER(SEARCH(OR("BKROCK","BLACKRCK" ,"BLACKROCK","BLACROCK","BLK RCK","BLKRCK","BLKRK","BLKRO","BLKROCK" ),B2:B1847)))) but it just returns the value 0. Does anyone know what I'm doing wrong and if there is another way around it? Thanks! -- blivy =SUMPRODUCT((#other criteria#)*(LEFT(B2:B1847,2)="BL")) On Tuesday, August 7, 2012 4:09:17 AM UTC-5, blivy wrote: Hi, I'm trying to count the number of entries in a range B2:B1847 that are either BKROCK BLACKRCK BLACKROCK BLACROCK BLK RCK BLKRCK BLKRK BLKRO BLKROCK (note that these all have numerous strings of differing amounts of numbers on the end so I cannot use a RIGHT function) The formula I had been using up until now was =SUMPRODUCT((#other criteria#)*(LEFT(B2:B1847,2)="BL")) but this returns BLACK RIVER which I don't want to count and doesn't include BKROCK OR BLKRO. I also tried the following: =SUMPRODUCT((#other criteria#)*(ISNUMBER(SEARCH("CK",B2:B1847)))) but this counts MAVERICK CAPITAL as well and not BLKRO. I wondered if I could perhaps combine a SEARCH and OR function as follows =SUMPRODUCT((#other criteria#)*(ISNUMBER(SEARCH(OR("BKROCK","BLACKRCK" ,"BLACKROCK","BLACROCK","BLK RCK","BLKRCK","BLKRK","BLKRO","BLKROCK" ),B2:B1847)))) but it just returns the value 0. Does anyone know what I'm doing wrong and if there is another way around it? Thanks! -- blivy Change this to suit your ACTUAL NAMES using the array { } =SUMPRODUCT((#other criteria#)*(LEFT(B2:B1847,2)={"BL","b","c"})) |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Search for either one or the other
Hi,
Am Tue, 7 Aug 2012 09:09:17 +0000 schrieb blivy: I wondered if I could perhaps combine a SEARCH and OR function as follows =SUMPRODUCT((#other criteria#)*(ISNUMBER(SEARCH(OR("BKROCK","BLACKRCK" ,"BLACKROCK","BLACROCK","BLK RCK","BLKRCK","BLKRK","BLKRO","BLKROCK" ),B2:B1847)))) but it just returns the value 0. try: =COUNT(SEARCH({"BKROCK","BLACKRCK","BLACKROCK","BL ACROCK","BLKRCK","BLKRK","BLKRO","BLKROCK"},B2:B18 47)) and enter the formula with CRTL+Shift+Enter Regards Claus Busch -- Win XP PRof SP2 / Vista Ultimate SP2 Office 2003 SP2 /2007 Ultimate SP2 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Search for either one or the other
On Tue, 7 Aug 2012 09:09:17 +0000, blivy wrote:
Hi, I'm trying to count the number of entries in a range B2:B1847 that are either BKROCK BLACKRCK BLACKROCK BLACROCK BLK RCK BLKRCK BLKRK BLKRO BLKROCK (note that these all have numerous strings of differing amounts of numbers on the end so I cannot use a RIGHT function) I assume these strings are only a portion of the cells contents. If you have a list of the desired substrings in some range, e.g: K1:K9, you can try this formula for a case INsensitive match: =SUMPRODUCT(COUNTIF($B$2:$B$1847,"*"&$K$1:$K$9&"*" )) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Search string to search for wild terms in Access database | Excel Programming | |||
Set up search box to, by default, search workbook in the extraoptions? | Excel Programming | |||
Search lastname + firstname (search on uppercase) | Excel Programming | |||
How do I search excel spreadsheets using multiple search criteria. | Excel Worksheet Functions | |||
Create a search Field within a worksheet to search command buttons | Excel Programming |