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!

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

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

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

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