string compare with wildcards

[Marcus Hoffmann]

Hi folks,

may be a stupid question, I want to use the following formula to fill a cell:

{=SUM((D3113="STRING")*B3:B113)}

which works fine, but accepting all kinds of "STRING*",
like: "STRING1", "STRING2222",...

is there an easy way ???

thanx in advance
marcus

 

[Bob Phillips]

Marcus,

Use

=SUM((LEFT(D3113,6)="STRING")*(B3:B113))

as an array formula, or

=SUMPRODUCT(--(LEFT(D3113,6)="STRING"),B3:B113)

as a non-array formula.

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[keepITcool]

Marcus,

try SEARCH worksheet function...

for your purpose:
=SUMPRODUCT(--NOT(ISERROR(SEARCH("string";D3113)));B3:B113)

notes:
search is case INSENSITIVE
search will accept arrays in both 1st and 2nd argument.
-- is a unary minus operator used to convert the boolean to a number.
FWIW: NO need to enter sumproduct as an array formula.


HTH... but fairly sure it does


--
keepITcool
| www.XLsupport.com | keepITcool chello nl | amsterdam


Marcus Hoffmann wrote :

 

[keepITcool]

another note:

search will also find "myString" so be carefull if that
is not desired. Since it will return the position
in the teststring you'll just have to test that it returns 1

pity it returns an error iso a simple 0 if the string
is not found.



--
keepITcool
| www.XLsupport.com | keepITcool chello nl | amsterdam


keepITcool wrote :