The three most-used aggregation functions in Excel — here's the exact situation each one is built for.
=SUM(B2:B100)
-- Adds all numbers in the range
-- No conditions, no filtering — just the total=SUMIF(criteria_range, criteria, sum_range)
=SUMIF(A2:A100, "North", B2:B100)
-- Sum of B values where A = "North"
=SUMIF(B2:B100, ">1000")
-- Sum of values in B that are over 1000 (range and sum_range are the same)=SUMIFS(sum_range, criteria_range1, criteria1, ...)
=SUMIFS(C2:C100, A2:A100, "North", B2:B100, "Active")
-- Sum of C where A = "North" AND B = "Active"
=SUMIFS(C2:C100, A2:A100, "North", D2:D100, ">="&DATE(2026,1,1))
-- North AND date on or after 1 Jan 2026SUMIF: criteria_range first, then criteria, then sum_range. SUMIFS: sum_range first, then criteria pairs. This inconsistency between SUMIF and SUMIFS trips up almost everyone.
Use SUMIFS by default — it handles one or multiple conditions and replaces SUMIF entirely. Use SUM for unconditional totals. You don't really need SUMIF anymore.
ExcelPro has 880+ hands-on exercises across 9 tracks — type real formulas, get instant feedback.
Start practising free →