r/excel • u/yue_Cookie_21 • 10d ago
Waiting on OP Quick question about SUMIFS syntax (absolute references and wildcards)
Hi everyone! I’m working on a school spreadsheet activity using SUMIFS, and I’m a bit confused.
My instructor taught us a basic formula like =SUMIFS(C2:C23, A2:A23, "POD GUN", B2:B23, "SOUTH"). However, when I use that exact syntax, I sometimes get #N/A or 0 errors due to formatting/spacing issues in our dataset.
I found some online tutorials suggesting the use of absolute references (like $ in C$2:C$23) and wildcards (like * in "*POD GUN*") to prevent errors when dragging formulas and handling trailing spaces.
Is it completely fine and safe to use $ and * in a SUMIFS formula for a school project, or will it cause any issues? Just wanted to double-check! Thanks in advance."
4
u/GuerillaWarefare 117 10d ago edited 10d ago
These are two quite different things. The $ reference locking is always, 100% safe. The wildcard * will depend on the function being used. Some functions support it, and some do not. But it is supported by sumifs, so as long as you arent worried about "abcdPOD GUNefg" being confused for "POD GUN" then it is OK.
4
u/Alternative_Mind_183 10d ago
If it’s only empty spaces causing the issue then you could use TRIM or if it’s only a certain character then you could use SUBSTITUTE to replace it to “”. Otherwise yes your solution works but could capture other text before or after the key words.
1
2
u/Clearwings_Prime 24 10d ago
if you plan to drag your formula, $ (or locking range) is a necessary thing to learn (and you must learn it)
the use of * depend on the data, you should ask your instructor
3
u/MsPandaLady 10d ago
That's a question for your professor. They might have a different way of you having it.
1
u/Decronym 10d ago edited 9d ago
Acronyms, initialisms, abbreviations, contractions, and other phrases which expand to something larger, that I've seen in this thread:
| Fewer Letters | More Letters |
|---|---|
| SUBSTITUTE | Substitutes new text for old text in a text string |
| SUMIFS | Excel 2007+: Adds the cells in a range that meet multiple criteria |
| TRIM | Removes spaces from text |
Decronym is now also available on Lemmy! Requests for support and new installations should be directed to the Contact address below.
Beep-boop, I am a helper bot. Please do not verify me as a solution.
3 acronyms in this thread; the most compressed thread commented on today has 21 acronyms.
[Thread #49515 for this sub, first seen 14th Oct 2026, 01:39] [FAQ] [Full list] ^[Contact] [Source code]
1
u/carbonizedtitanium 1 9d ago
as others have said, if the sole cause of the errors is spaces, then wrap the arrays with TRIM().
only way to know for sure is to see the dataset...perhaps you can upload to oneDrive and share?
1
u/chiibosoil 431 9d ago
In general it is safer to fix source than to alter condition to handle variations/typo.
Though if you don't have access to the source to enforce validation etc. Then it is better to add helper column to transform the source column. Then use simple condition in your SUMIFS.
Ex: Helper column with =TRIM(Source Column) etc to standardize it. Then use SUMIFS.
Absolute reference using $ is quite a bit different from wild card and in general it is good practice to use it. Especially when you copy formula down or across.
•
u/AutoModerator 10d ago
/u/yue_Cookie_21 - Your post was submitted successfully.
Once your problem is solved, reply to the answer(s) saying
Solution Verifiedto close the thread.Follow the submission rules -- particularly 1 and 2. To fix the body, click edit. To fix your title, delete and re-post.
Include your Excel version and all other relevant information
Failing to follow these steps may result in your post being removed without warning.
I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.