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