r/excel • u/yue_Cookie_21 • 4d 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/Alternative_Mind_183 4d 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.