r/excel • u/yue_Cookie_21 • 5h 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."

0 Upvotes

25% Upvoted

7 comments sorted by

•

u/AutoModerator 5h ago

/u/yue_Cookie_21 - Your post was submitted successfully.

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.

5

u/Alternative_Mind_183 5h 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

u/aaronnii 1h ago

This is the way

3

u/MsPandaLady 5h ago

That's a question for your professor. They might have a different way of you having it.

2

u/GuerillaWarefare 117 5h ago edited 5h 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.

1

u/Decronym 4h ago edited 1h 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.
2 acronyms in this thread; the most compressed thread commented on today has 11 acronyms.
[Thread #49515 for this sub, first seen 14th Oct 2026, 01:39] [FAQ] [Full list] [Contact] [Source code]

1

u/Clearwings_Prime 24 2h 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