Excel tutorial
How to use SUMIFS with multiple criteria
A good SUMIFS formula starts with a clear question. Once you know what you want to add and which rows count, the syntax becomes much easier.
Start with the value you want to add
The first argument in SUMIFS is the sum range. This should be the numeric column you want to total, such as Revenue, Hours, Quantity, or Cost.
After that, add each condition as a pair: the range to check, then the value or rule it must match.
Add one condition at a time
Build the formula gradually. First confirm the total for one criterion, then add the second and third criteria.
This prevents a common problem: writing a long formula that returns zero, then not knowing which condition filtered everything out.
<FormulaExample title="Date window" formula="=SUMIFS(E:E,D:D,">="&G1,D:D,"<="&H1)" explanation="Totals values where the date is between the start date in G1 and end date in H1." />
Practical examples
Region and product
=SUMIFS(E:E,B:B,"North",C:C,"Laptop")Totals column E for rows where region is North and product is Laptop.
Recommended Excel functions
Common mistakes
- 01Hardcoding criteria when a cell reference would make the formula reusable.
- 02Using a date as text instead of comparing against a real date value.
- 03Adding criteria that refer to different tables or misaligned rows.
Practice
Practice this skill with interactive exercises on Calcu.
Use Calcu to practice multi-criteria SUMIFS in realistic spreadsheet exercises with instant feedback.
FAQ
Can SUMIFS handle more than two criteria?
Yes. Add more criteria_range and criteria pairs after the first pair.
Why does my SUMIFS return zero?
Usually one criterion does not match the data exactly, or a date/number is being compared as text.