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!

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

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"}))

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

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&"*" ))