How to avoid '*' being used as wildcard in SUMIF?
in [General]
|
From: Kealkil4 on 1 Feb 2010 12:23 A data field for which I wish to do a SUMIF begins with the character "*". e.g I have a Product Code of "*SPEC" where the * is the first character of a 5 character code. When I do a SUMIF the answer includes any row where the Range value ends with the characters "SPEC". I do not want the "*" being used as a wildcard in this instance. All Suggestions welcome. Issue arises in both Excel 2003 and Excel 2007.
From: joemeshuggah on 1 Feb 2010 12:43 are you using quotes? e.g. =SUMIF(A:A,"*SPEC",B:B) "Kealkil4" wrote: > A data field for which I wish to do a SUMIF begins with the character "*". > e.g I have a Product Code of "*SPEC" where the * is the first character of a > 5 character code. When I do a SUMIF the answer includes any row where the > Range value ends with the characters "SPEC". > > I do not want the "*" being used as a wildcard in this instance. > > All Suggestions welcome. > > Issue arises in both Excel 2003 and Excel 2007.
From: "David Biddulph" groups [at] on 1 Feb 2010 12:41 Precede you asterisk with a tilde ~, so use "~*SPEC". -- David Biddulph "Kealkil4" <Kealkil4(a)discussions.microsoft.com> wrote in message news:771704A0-D59C-4D02-9F69-AAB249405403(a)microsoft.com... >A data field for which I wish to do a SUMIF begins with the character "*". > e.g I have a Product Code of "*SPEC" where the * is the first character of > a > 5 character code. When I do a SUMIF the answer includes any row where the > Range value ends with the characters "SPEC". > > I do not want the "*" being used as a wildcard in this instance. > > All Suggestions welcome. > > Issue arises in both Excel 2003 and Excel 2007.
From: joemeshuggah on 1 Feb 2010 12:45 disregard my last post and try =SUMIF(A:A,"~*SPEC",B:B) "Kealkil4" wrote: > A data field for which I wish to do a SUMIF begins with the character "*". > e.g I have a Product Code of "*SPEC" where the * is the first character of a > 5 character code. When I do a SUMIF the answer includes any row where the > Range value ends with the characters "SPEC". > > I do not want the "*" being used as a wildcard in this instance. > > All Suggestions welcome. > > Issue arises in both Excel 2003 and Excel 2007.
From: Eduardo on 1 Feb 2010 12:47
Hi, The only way I can see is that you have a list of your porduct codes in another column where you perform the sum, i.e. column H you have the list of products starting H2, then your information is from column A to E where A is your product code and E is the column to summarize, so in column I enter =sumproduct(--(H2=$A$1:$A$10000),$E$1:$E$10000) copy formula down, change ranges to match yours "Kealkil4" wrote: > A data field for which I wish to do a SUMIF begins with the character "*". > e.g I have a Product Code of "*SPEC" where the * is the first character of a > 5 character code. When I do a SUMIF the answer includes any row where the > Range value ends with the characters "SPEC". > > I do not want the "*" being used as a wildcard in this instance. > > All Suggestions welcome. > > Issue arises in both Excel 2003 and Excel 2007. |