Excel's SUMIFS function adds up the numbers in one range, but only for rows where every condition you set is true. This guide covers the argument order, two working formulas from Microsoft, and how to handle the asterisk and question mark.
The SUMIFS syntax, argument by argument
Microsoft's SUMIFS reference page gives the syntax as:
SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...)
- sum_range holds the numbers you want added. It always comes first.
- criteria_range1 is the column Excel checks for your first condition.
- criteria1 is the condition itself.
- [criteria_range2, criteria2], ... are optional extra pairs.
Think of everything after the first argument as range-and-condition pairs. A row is added only when it passes every pair.
Build a SUMIFS formula step by step
- Click the cell where you want the total.
- Type
=SUMIFS(and select the range of numbers to add, then type a comma. - Select the first range to check, type a comma, then type the condition in double quotes, such as
"Tom". - For each extra condition, type a comma, select its range, type a comma, and type its condition.
- Close the bracket and press Enter.
Two formulas from Microsoft's example
Microsoft's page uses a sheet with amounts in A2:A9, products in B2:B9 and salespeople in C2:C9. The first example is:
=SUMIFS(A2:A9,B2:B9,"=A*",C2:C9,"Tom")
This adds the amounts in A2:A9 where the product in column B begins with the letter A and the name in column C is Tom. The * stands in for any following characters.
The second example swaps the first condition for a "not equal" test:
=SUMIFS(A2:A9,B2:B9,"<>Bananas",C2:C9,"Tom")
Here <> means "not equal to", so the formula adds Tom's amounts for every product except Bananas. The operator goes inside the quotes with the value.
When you need to match a real asterisk or question mark
Because * and ? act as wildcards in criteria, a condition like "Q?" won't look for a literal question mark. Microsoft says to type a tilde (~) in front of the character to match it literally.
=SUMIFS(A2:A9,B2:B9,"Q~?")
The same goes for an asterisk: ~* matches a real *.
The mistake that breaks most SUMIFS formulas
The usual error is putting the range to check first and the numbers last. In SUMIFS, the range you're adding is always the first argument, and every argument after it comes in range-then-condition pairs. If your total looks wrong, read the formula left to right and confirm the first range holds numbers and each condition follows its own range.
For argument rules, error causes and version notes, check Microsoft's SUMIFS page directly. If you only need to count matching rows rather than add them, the logic is similar to COUNTIF in Google Sheets. To pull a single matching value instead of a total, use XLOOKUP.
