SUMIFS in Excel: what it does and how to use it
SUMIFS adds up the numbers in one column, but only on the rows that satisfy every condition you give it. Conditions come in pairs: the column to test, then what to test for.
Syntax
SUMIFS(sum_range, criteria_range1, criteria1, ...)
When to use it
Any total that comes with the word "only". Sales for one customer, hours for one project, invoices inside one date range.
What trips people up
The range being added comes first, and every condition after it must cover exactly the same rows. Mismatched ranges are the usual cause of a total that looks close but is not right. For dates, build the boundaries with DATE rather than typing them as text.
Watch it built, step by step
Questions
How do I add up a date range?
Test the date column twice: =SUMIFS(B:B,A:A,">="&DATE(2026,1,1),A:A,"<="&DATE(2026,1,31)) adds everything in January.
Can SUMIFS add rows that match one value or another?
Not by itself, since every condition has to hold. Add two SUMIFS together, or give it a list and wrap it in SUM: =SUM(SUMIFS(B:B,A:A,{"North","South"})).
Why does SUMIFS return 0?
No row meets every condition at once. The usual causes are dates stored as text, a stray space in the data, or ranges that cover different rows.
Related functions
- SUMIF — SUMIF adds up the numbers in a column, but only on the rows that meet one condition you set.
- COUNTIFS — COUNTIFS counts the rows where every condition you give it is true at the same time.
- MAXIFS — MAXIFS returns the largest number in a column, but only from the rows that meet the conditions you set.
