Ignoring everthing after a hypen

In a sumproduct formula I have a criteria that is separated by a "-".

Example:

20001-345
20001-456
20001-500

I want to include all numbers that start with 20001 in the criteria
regardless of anything that follows it. Do I convert it to text and
surround 20001 in " "s?

Thanks for any and all help!!

Steve

Hi!

What's your formula actually doing?

You could do both:

LEFT(A1,5)="20001"

OR

LEFT(A1,5)*1=20001

OR

--LEFT(A1,5)=20001

OR

ISNUMBER(SEARCH(20001,A1))

Biff

"Steve" <[email protected]> wrote in message
news:[email protected]...
> In a sumproduct formula I have a criteria that is separated by a "-".
>
> Example:
>
> 20001-345
> 20001-456
> 20001-500
>
> I want to include all numbers that start with 20001 in the criteria
> regardless of anything that follows it. Do I convert it to text and
> surround 20001 in " "s?
>
> Thanks for any and all help!!
>
> Steve

My formula is this...where 2155 the criteria I want to include despite the
fact that in Column C there are multiply variations of that number such
as:2155-145, 2155-347, etc...... sometimes (this sheet is part of 110 other
sheets and they are filled out by various people sooooooo.... sometimes they
put 2155.145 and sometimes they put in a space between the hyphens so there
are many variations.... that I would like to include as long as they start
with 2155.

=SUMIF(C4:C40,2155,D4:D40)

Does this info change things? I know in searching for things you can put
*.* to do this but it doesn't seem to work here.

Thanks a million for your help


"Biff" wrote:

> Hi!
>
> What's your formula actually doing?
>
> You could do both:
>
> LEFT(A1,5)="20001"
>
> OR
>
> LEFT(A1,5)*1=20001
>
> OR
>
> --LEFT(A1,5)=20001
>
> OR
>
> ISNUMBER(SEARCH(20001,A1))
>
> Biff
>
> "Steve" <[email protected]> wrote in message
> news:[email protected]...
> > In a sumproduct formula I have a criteria that is separated by a "-".
> >
> > Example:
> >
> > 20001-345
> > 20001-456
> > 20001-500
> >
> > I want to include all numbers that start with 20001 in the criteria
> > regardless of anything that follows it. Do I convert it to text and
> > surround 20001 in " "s?
> >
> > Thanks for any and all help!!
> >
> > Steve
>
>
>

Hi!

OK, since some entries may be numeric - 2155.145, and some may be text -
2155-145, Sumif won't work (even with wildcards), so your best choice is:

=SUMPRODUCT(--(LEFT(C4:C40,4)="2155"),D4:D40)

Biff

"Steve" <[email protected]> wrote in message
news:[email protected]...
> My formula is this...where 2155 the criteria I want to include despite the
> fact that in Column C there are multiply variations of that number such
> as:2155-145, 2155-347, etc...... sometimes (this sheet is part of 110
> other
> sheets and they are filled out by various people sooooooo.... sometimes
> they
> put 2155.145 and sometimes they put in a space between the hyphens so
> there
> are many variations.... that I would like to include as long as they start
> with 2155.
>
> =SUMIF(C4:C40,2155,D4:D40)
>
> Does this info change things? I know in searching for things you can put
> *.* to do this but it doesn't seem to work here.
>
> Thanks a million for your help
>
>
> "Biff" wrote:
>
>> Hi!
>>
>> What's your formula actually doing?
>>
>> You could do both:
>>
>> LEFT(A1,5)="20001"
>>
>> OR
>>
>> LEFT(A1,5)*1=20001
>>
>> OR
>>
>> --LEFT(A1,5)=20001
>>
>> OR
>>
>> ISNUMBER(SEARCH(20001,A1))
>>
>> Biff
>>
>> "Steve" <[email protected]> wrote in message
>> news:[email protected]...
>> > In a sumproduct formula I have a criteria that is separated by a "-".
>> >
>> > Example:
>> >
>> > 20001-345
>> > 20001-456
>> > 20001-500
>> >
>> > I want to include all numbers that start with 20001 in the criteria
>> > regardless of anything that follows it. Do I convert it to text and
>> > surround 20001 in " "s?
>> >
>> > Thanks for any and all help!!
>> >
>> > Steve
>>
>>
>>

Hi!

Another thing that you could do is "force" all users to enter data in a
uniform style!

Maybe setup data validation that will only accept decimal values. Data
validation is easily "defeated", though!

Biff

"Biff" <[email protected]> wrote in message
news:%[email protected]...
> Hi!
>
> OK, since some entries may be numeric - 2155.145, and some may be text -
> 2155-145, Sumif won't work (even with wildcards), so your best choice is:
>
> =SUMPRODUCT(--(LEFT(C4:C40,4)="2155"),D4:D40)
>
> Biff
>
> "Steve" <[email protected]> wrote in message
> news:[email protected]...
>> My formula is this...where 2155 the criteria I want to include despite
>> the
>> fact that in Column C there are multiply variations of that number such
>> as:2155-145, 2155-347, etc...... sometimes (this sheet is part of 110
>> other
>> sheets and they are filled out by various people sooooooo.... sometimes
>> they
>> put 2155.145 and sometimes they put in a space between the hyphens so
>> there
>> are many variations.... that I would like to include as long as they
>> start
>> with 2155.
>>
>> =SUMIF(C4:C40,2155,D4:D40)
>>
>> Does this info change things? I know in searching for things you can put
>> *.* to do this but it doesn't seem to work here.
>>
>> Thanks a million for your help
>>
>>
>> "Biff" wrote:
>>
>>> Hi!
>>>
>>> What's your formula actually doing?
>>>
>>> You could do both:
>>>
>>> LEFT(A1,5)="20001"
>>>
>>> OR
>>>
>>> LEFT(A1,5)*1=20001
>>>
>>> OR
>>>
>>> --LEFT(A1,5)=20001
>>>
>>> OR
>>>
>>> ISNUMBER(SEARCH(20001,A1))
>>>
>>> Biff
>>>
>>> "Steve" <[email protected]> wrote in message
>>> news:[email protected]...
>>> > In a sumproduct formula I have a criteria that is separated by a "-".
>>> >
>>> > Example:
>>> >
>>> > 20001-345
>>> > 20001-456
>>> > 20001-500
>>> >
>>> > I want to include all numbers that start with 20001 in the criteria
>>> > regardless of anything that follows it. Do I convert it to text and
>>> > surround 20001 in " "s?
>>> >
>>> > Thanks for any and all help!!
>>> >
>>> > Steve
>>>
>>>
>>>
>
>

Thanks Biff!!

"Biff" wrote:

> Hi!
>
> OK, since some entries may be numeric - 2155.145, and some may be text -
> 2155-145, Sumif won't work (even with wildcards), so your best choice is:
>
> =SUMPRODUCT(--(LEFT(C4:C40,4)="2155"),D4:D40)
>
> Biff
>
> "Steve" <[email protected]> wrote in message
> news:[email protected]...
> > My formula is this...where 2155 the criteria I want to include despite the
> > fact that in Column C there are multiply variations of that number such
> > as:2155-145, 2155-347, etc...... sometimes (this sheet is part of 110
> > other
> > sheets and they are filled out by various people sooooooo.... sometimes
> > they
> > put 2155.145 and sometimes they put in a space between the hyphens so
> > there
> > are many variations.... that I would like to include as long as they start
> > with 2155.
> >
> > =SUMIF(C4:C40,2155,D4:D40)
> >
> > Does this info change things? I know in searching for things you can put
> > *.* to do this but it doesn't seem to work here.
> >
> > Thanks a million for your help
> >
> >
> > "Biff" wrote:
> >
> >> Hi!
> >>
> >> What's your formula actually doing?
> >>
> >> You could do both:
> >>
> >> LEFT(A1,5)="20001"
> >>
> >> OR
> >>
> >> LEFT(A1,5)*1=20001
> >>
> >> OR
> >>
> >> --LEFT(A1,5)=20001
> >>
> >> OR
> >>
> >> ISNUMBER(SEARCH(20001,A1))
> >>
> >> Biff
> >>
> >> "Steve" <[email protected]> wrote in message
> >> news:[email protected]...
> >> > In a sumproduct formula I have a criteria that is separated by a "-".
> >> >
> >> > Example:
> >> >
> >> > 20001-345
> >> > 20001-456
> >> > 20001-500
> >> >
> >> > I want to include all numbers that start with 20001 in the criteria
> >> > regardless of anything that follows it. Do I convert it to text and
> >> > surround 20001 in " "s?
> >> >
> >> > Thanks for any and all help!!
> >> >
> >> > Steve
> >>
> >>
> >>
>
>
>