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

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.

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

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

"Kealkil4" wrote in message
...
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.

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

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.

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

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.

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

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.

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

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" wrote in message
...
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.

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

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