FormuladrillSUMIF
SUMIF(range, criteria, [sum_range]) adds the cells in sum_range whose matching cell in range meets one condition, such as "North" or ">500". If sum_range is omitted the cells in range itself are summed. For two or more conditions use SUMIFS.
Syntax
SUMIF(range, criteria, [sum_range])| Part | What it is |
|---|---|
range | The cells to test against the criteria. |
criteria | The condition: a value, a cell reference, or text like ">500", "<>North", "*shirt*". |
sum_range | The numbers to add. Same size as range. Omit it to sum range itself. |
Examples
=SUMIF(A2:A100, "North", C2:C100)Result: Total of column C for rows where column A is North
Text criteria are not case sensitive.
=SUMIF(C2:C100, ">500")Result: Total of all values in C2:C100 above 500
No sum_range, so the tested column is summed.
=SUMIF(A2:A100, E2, C2:C100)Result: Total for whichever region is typed in E2
Put the criteria in a cell so you can change it without editing the formula.
=SUMIF(B2:B100, "*shirt*", C2:C100)Result: Total for products whose name contains shirt
* matches any text, ? matches one character.
Common mistakes
- The total is 0 for a numeric condition: the condition must be a text string, =SUMIF(C:C, ">" & E2) not =SUMIF(C:C, > E2).
- Wrong total when range and sum_range are different sizes: Excel lines sum_range up from its first cell, so start both on the same row.
- 0 when the criterion is a date: compare with a real date, for example ">=" & DATE(2026, 1, 1), not ">=1/1/2026" typed as text in some locales.
- SUMIF ignores numbers stored as text in sum_range. Convert them with VALUE or multiply by 1.
Practice SUMIF
These drills check your answer on live data, free, no account needed.
- Sales for one regionThe region to report on is typed in E2. Put the total sales for that region in E3. Refer to E2 rather than typing the region name, so the answer follows if E2 changes.
Questions
What is the difference between SUMIF and SUMIFS?
SUMIF takes one condition and the sum range comes last. SUMIFS takes any number of conditions and the sum range comes first. SUMIFS can do everything SUMIF does, so many people only use SUMIFS.
How do I SUMIF with greater than a cell value?
Join the operator and the cell with &: =SUMIF(C2:C100, ">" & E2). The same works for dates: ">=" & E2.
Can SUMIF use OR conditions?
Yes, add two SUMIFs or use SUMPRODUCT(SUMIF(A:A, {"North","South"}, C:C)), which sums both regions in one formula.
Related
- SUMIFSHow to use SUMIFS in Excel to add values that meet two or more conditions, with examples for region and status, date ranges, not-equal and wildcards.
- COUNTIFHow to use COUNTIF in Excel to count cells equal to a value, above a number, containing text or not blank, with examples, a duplicate check and fixes.
- AVERAGEIFHow to use AVERAGEIF and AVERAGEIFS in Excel to average only the cells that match a condition, with examples for one region, positive values and two criteria.
- SUMPRODUCTHow the Excel SUMPRODUCT function multiplies ranges and sums the result, with examples for quantity times price, weighted average and sums with conditions.