Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
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" wrote in message ... 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 |
#3
|
|||
|
|||
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" wrote in message ... 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 |
#4
|
|||
|
|||
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" wrote in message ... 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" wrote in message ... 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 |
#5
|
|||
|
|||
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" wrote in message ... 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" wrote in message ... 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" wrote in message ... 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 |
#6
|
|||
|
|||
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" wrote in message ... 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" wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
calculating the minimum value ignoring o | Excel Worksheet Functions | |||
Sort ignoring negatives and positives | Excel Worksheet Functions | |||
Ignoring Lines with Errors in Pivot Tables | Excel Worksheet Functions | |||
Averaging noncontiguous numbers ignoring zeros? | Excel Worksheet Functions | |||
Averaging, ignoring zeros | Excel Worksheet Functions |