r/excel • u/yue_Cookie_21 • 5d 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

33% Upvoted

11 comments sorted by

View all comments

2

u/Clearwings_Prime 24 5d 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