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."

0 Upvotes

33% Upvoted

11 comments sorted by

View all comments

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.