Search for either one or the other

B

blivy

Hi,

I'm trying to count the number of entries in a range B2:B1847 that ar
either
BKROCK
BLACKRCK
BLACKROCK
BLACROCK
BLK RCK
BLKRCK
BLKRK
BLKRO
BLKROCK
(note that these all have numerous strings of differing amounts o
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'
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 a
follows
=SUMPRODUCT((#othe
criteria#)*(ISNUMBER(SEARCH(OR("BKROCK","BLACKRCK","BLACKROCK","BLACROCK","BL
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 aroun
it?
Thanks
 
D

Don Guillett

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!
Click to expand...
 
D

Don Guillett

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!
Click to expand...

=SUMPRODUCT((#other criteria#)*(LEFT(B2:B1847,2)="BL"))

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!
Click to expand...
Change this to suit your ACTUAL NAMES using the array { }

=SUMPRODUCT((#other criteria#)*(LEFT(B2:B1847,2)={"BL","b","c"}))
 
C

Claus Busch

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.
Click to expand...

try:
=COUNT(SEARCH({"BKROCK","BLACKRCK","BLACKROCK","BLACROCK","BLKRCK","BLKRK","BLKRO","BLKROCK"},B2:B1847))
and enter the formula with CRTL+Shift+Enter


Regards
Claus Busch
 
R

Ron Rosenfeld

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)
Click to expand...

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