Which formula to match string subset?

  • Thread starter Thread starter David Lipetz
  • Start date Start date
D

David Lipetz

I'm being obtuse today and can't figure out which formula to use to match
the beginning portion of a string.

For instance, in a SUMPRODUCT or IF statement I need to be able to identify
just those records where the first few characters of value in cell within a
named range matches what is specified in the formula.

Named Range = Freight Named Range=FtChg
GR1234 125.00
2D9879 300.00
GR3467 50.00
SA0009 285.00
GR0099 100.00

Using SUMPRODUCT, I'd like to be able to look at just the rows where Freight
starts with "GR". Same for use in IF statements.

I think I can use something like
=SUMPRODUCT(--(LEFT(Freight,2)="GR"),(FtChg)) but there is probably
something simpler?

Thanks,
David
 
=SUMIF(Freight,"GR*",FtChg)

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)
 
Duh! Wildcards. Can't believe I missed that.

Thanks - works for SUMIF as you pointed out, but it seems as though
wildcards don't work in SUMPRODUCT or IF.

I don't think SUMIF will work for me as I need multiple parameters (eg.
Inv="Normal, MONTH(DATE=3), etc).

Also, what about using wildcard for an IF statement. I have a single cell
(C179) that I want to evaluate. If the value of C179 starts with "EXP" I
want to display some text. I tried:

=IF($C$179="EXP*","Shipping & Handling is applicable and wil be added to
Invoice","")

But the formula above results in FALSE even when C179 does indeed start with
EXP.

Thanks,
David
 
No, as you have found it doesn't, you need other tricks, like the
LEFT($C$179,3). That is the simplest method.
 
Another way:

=IF(ISNUMBER(SEARCH("xyz",A1)),"yes","no")

will return yes if xyz is contained within A1. You can build this into
more complex conditions.

Hope this helps.

Pete
 
Back
Top