How to avoid '*' being used as wildcard in SUMIF?

[Kealkil4]

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.

 

[David Biddulph]

Precede you asterisk with a tilde ~, so use "~*SPEC".

 

[joemeshuggah]

are you using quotes?

e.g.
=SUMIF(A:A,"*SPEC",B:B)

 

[joemeshuggah]

disregard my last post and try

=SUMIF(A:A,"~*SPEC",B:B)

 

[Eduardo]

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

 

[T. Valko]

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.

 

[Dave Peterson]

And just to add to show all the wildcard/escape characters:

~?
~*
~~