How to avoid '*' being used as wildcard in SUMIF?
in [General]
|
From: T. Valko on 1 Feb 2010 12:49 Try it like this... =SUMIF(A1:A10,"~*spec",B1:B10) The tilde is an "escape character" that tells Excel to treat the astrisk as the literal asterisk character and not a wildcard. -- Biff Microsoft Excel MVP "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: Dave Peterson on 1 Feb 2010 13:42
And just to add to show all the wildcard/escape characters: ~? ~* ~~ 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. -- Dave Peterson |