Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
sumif wildcard criteria | Excel Worksheet Functions | |||
sumif wildcard criteria | Excel Worksheet Functions | |||
Wildcard with sumif | Excel Discussion (Misc queries) | |||
Sumif using wildcard | Excel Worksheet Functions | |||
SUMIF, wildcard and cell ref | Excel Worksheet Functions |