Blog / Formulas
SUMIF vs SUMIFS: what's the difference?
3 November 2026 ยท 1 min read
When to use SUMIF and when to use SUMIFS in Excel, why their arguments are in a different order, and the mistakes that trip people up.
SUMIF and SUMIFS both add up the numbers that meet a condition. The difference is how many conditions they take, and the order of their arguments, which is where most mistakes come from.
SUMIF: one condition
=SUMIF(range, criteria, sum_range)
=SUMIF(B2:B100, "East", D2:D100) adds the amounts in column D for rows where column B says East.
The range to test comes first and the range to add comes last. If you leave out the sum range, SUMIF adds the tested cells themselves, which is handy for something like =SUMIF(D2:D100, ">500").
SUMIFS: several conditions
=SUMIFS(sum_range, criteria_range1, criteria1, criteria_range2, criteria2, ...)
=SUMIFS(D2:D100, B2:B100, "East", D2:D100, ">500") adds East amounts that are over 500.
Here the range to add comes first, followed by pairs of a range and its condition. A row is only added when every condition is true.
Which should you use?
SUMIFS works with a single condition too, so many people simply use SUMIFS everywhere. Its argument order is the same as COUNTIFS, AVERAGEIFS and MAXIFS, which makes them easier to remember together.
Mistakes to avoid
- Mixing up the argument order. In SUMIF the sum range is last; in SUMIFS it is first.
- Forgetting quotes around comparisons. Write
">500", not>500. - Comparing against a cell. To use a value from E1, join the operator and the cell:
">"&E1. Writing">E1"looks for the text E1. - Ranges of different sizes. Every range must cover the same rows, such as B2:B100 and D2:D100.
Date ranges
To total a period, test the date column twice:
=SUMIFS(D2:D100, A2:A100, ">="&G1, A2:A100, "<="&G2)
This adds amounts dated from G1 to G2, including both days.
Practise both in our conditional totals lesson, and see the guides for SUMIF and SUMIFS.
Practise it: Totals with conditions
Type the formulas yourself in a real spreadsheet, with instant feedback on every answer. Your first five lessons are free.