Use SUM to add numbers without applying a condition; use SUMIF to add numbers only when a specified condition is met. In short, SUM totals a range, while SUMIF totals a range selectively—for example, adding sales from one region but not others.
The Main Difference at a Glance
Both functions produce a total, but they answer different questions. SUM answers, “What is the total of these numbers?” SUMIF answers, “What is the total of the numbers associated with entries that meet this condition?” The second function checks one range for a criterion and adds corresponding values from a sum range.
| Function | What it does | Typical use |
|---|---|---|
| SUM | Adds the numbers in the ranges or values you specify. | Total every value in a sales, hours, or expense column. |
| SUMIF | Checks one range against one criterion and adds matching values. | Total sales for one region, or amounts above a threshold. |
SUM is usually the clearer choice when every value belongs in the result. SUMIF is useful when a total depends on a category or rule. Neither function changes the source data; each calculates a result from it.
How SUM Works
SUM adds numbers from one or more cells, ranges, or numeric values. You can use it for a single continuous column, combine separate ranges, or add a short list of individual cells. When a cell reference contains text, SUM ignores that text; it adds numeric values and returns an error if a referenced value that it needs to process is an error.
With monthly amounts in B2:B6, the formula below adds all five amounts. If the cells contain 120, 80, 45, 200, and 55, the result is 500.
With amounts in B2:B6, enter: =SUM(B2:B6)
SUM does not decide whether a row qualifies based on a neighboring label. If B2:B6 contains sales for several regions, the formula above still includes every numeric sale in that range. That predictable behavior makes SUM a good base for a simple grand total, but it cannot, by itself, answer a category-specific question.
How SUMIF Works
SUMIF uses three roles: the range Excel checks, the condition it checks for, and the range of values it adds. The check range and sum range are often adjacent columns, such as region names in A and sales amounts in B. Keep those ranges the same size and aligned row by row so each matching label is paired with its intended amount.
With regions in A2:A6 and their sales in B2:B6, and East in A2, A4, and A5, the following formula adds the corresponding amounts. For the example values 120, 80, 45, 200, and 55 in B2:B6, the East total is 365.
With regions in A2:A6 and sales in B2:B6, enter: =SUMIF(A2:A6,"East",B2:B6)
The criterion can be typed into the formula or supplied by a cell. Referencing a cell makes it easy to change the requested region without editing the formula. SUMIF can also check a numeric condition, such as amounts greater than 100, rather than a text category.
With sales amounts in B2:B6, the formula below adds only values greater than 100. For the example values above, it returns 320 because 120 and 200 qualify.
With amounts in B2:B6, enter: =SUMIF(B2:B6,">100")
One-Condition Examples You Can Adapt
A practical way to choose between the functions is to state the question before writing a formula. If the question is “What are all the sales?”, use SUM. If it is “What are the sales in East?”, use SUMIF. The same pattern applies to hours by employee, expenses by department, units by product, and payments by status.
For a reusable report, put the criterion in a separate cell. With regions in A2:A6, sales in B2:B6, and the region to total in D2, use the following. If D2 contains East, the example total is 365.
With regions in A2:A6, sales in B2:B6, and the criterion in D2, enter: =SUMIF(A2:A6,D2,B2:B6)
This setup is handy when a worksheet has a selector cell or a small summary table with one row per region. You can copy the formula down the summary table if its references are arranged appropriately. Check copied formulas to make sure Excel is evaluating the intended input and output ranges.
Criteria, Wildcards, and Dates
SUMIF accepts criteria for exact text or numbers, as well as comparison conditions. Operators such as greater than or equal to are written as part of a text criterion. Text matching is not case-sensitive, so a criterion for East also matches EAST or east. If capitalization must distinguish records, SUMIF alone is not a case-sensitive solution.
- For a text category, use the category name as the criterion, such as East.
- For a numeric comparison, put the comparison operator and value together as a criterion, such as greater than 100.
- The asterisk wildcard represents any number of characters, while the question mark represents one character. Use a tilde before a wildcard character when you need to match that character literally.
- For dates, use real Excel date values in the data rather than relying on dates stored as inconsistent text.
Date conditions can be less intuitive because Excel stores real dates as serial numbers, while a criterion that includes an operator is assembled as text. With dates in C2:C6 and amounts in B2:B6, the example below adds amounts dated on or after January 1, 2026. The comparison assumes C2:C6 contains actual Excel dates.
With dates in C2:C6 and amounts in B2:B6, enter: =SUMIF(C2:C6,">="&DATE(2026,1,1),B2:B6)
For a month-long period, it is generally clearer to define a start date and an exclusive end date than to rely on text matching. That avoids accidentally excluding entries with times attached to their dates. If the rule involves more than one condition—for example, East and Widget—use SUMIFS instead of trying to combine several tests into a single SUMIF criterion.
When to Use SUMIFS Instead
SUMIF handles one criterion. SUMIFS handles multiple criteria, such as a region and a product, or a department and a date period. One important difference is argument order: SUMIF starts with the criteria range, while SUMIFS starts with the sum range, followed by pairs of criteria ranges and criteria. That order is a frequent source of formula mistakes.
With regions in A2:A5, product names in B2:B5, and sales in C2:C5, suppose the four rows are East/Widget/120, East/Gadget/40, West/Widget/80, and East/Widget/200. The formula below totals East sales for Widget and returns 320.
With regions in A2:A5, products in B2:B5, and sales in C2:C5, enter: =SUMIFS(C2:C5,A2:A5,"East",B2:B5,"Widget")
If you find yourself wishing one SUMIF could check two independent columns, that is the cue to consider SUMIFS. For more complex rules, such as alternative conditions or calculations that cannot be expressed with the available criteria, a helper column or another approach may be easier to audit.
Common Problems and How to Fix Them
A SUMIF result that is zero or unexpectedly small does not always mean the function is wrong. First check the data and the ranges. A label may contain an extra space, a number may be stored as text, or the sum range may be offset from the criteria range. Using equally sized, row-aligned ranges prevents many accidental mismatches.
- Wrong argument order: In SUMIF, the criteria range comes first and the sum range comes last. If the values to add are in the criteria range itself, the sum range can be omitted.
- Numbers stored as text: A value that looks numeric may not behave like a number. Check the source cells and correct their data type if needed.
- Unexpected text match: SUMIF does not distinguish uppercase from lowercase. Also check for spaces or inconsistent labels in imported data.
- Dates do not match: Confirm that the cells contain real dates, and construct comparison criteria carefully. A date displayed in a familiar format may still be stored as text.
- Hidden rows: SUM and SUMIF normally include qualifying values in hidden rows. Hiding a row is not the same as excluding it from the calculation.
When a result looks wrong, test a small, visible sample and compare the qualifying rows with the total. Also check for errors in cells the formula references, since errors can affect the result. If the formula is copied across or down, inspect its ranges and any criteria-cell references to make sure they still point where you intend.
Choosing the Right Function
Choose based on the rule behind the total, not on which function seems more advanced. SUM is shorter and easier to read for an unconditional total. SUMIF adds a useful filter when one condition determines whether a value is included. SUMIFS is the natural next step when two or more conditions must all be satisfied.
- Use SUM for a grand total of a numeric range or selected cells.
- Use SUMIF for a total tied to one category, threshold, text pattern, or date condition.
- Use SUMIFS for totals that must meet several conditions at once.
- Keep the source data consistent and ranges aligned, especially when labels and amounts live in separate columns.
These functions are available in current Microsoft Excel for Microsoft 365 on Windows. The formulas shown use commas as argument separators, as is typical with those settings; Excel installations configured to use a different list separator may display semicolons instead. The calculation logic is the same. In Excel for the web and Excel for Mac, the functions work as well, although the interface and regional separator settings can differ.
Frequently Asked Questions
Can SUMIF add numbers in the same range it checks?
Yes. If the values being tested are also the values to add, the sum range can be omitted. For example, this is useful when adding only amounts that meet a numeric threshold in that same range.
Is SUMIF case-sensitive?
No. SUMIF text criteria are case-insensitive, so uppercase and lowercase versions of a label are treated as matches. Use another method if the distinction between capitalization matters.
What is the difference between SUMIF and SUMIFS?
SUMIF applies one criterion; SUMIFS can apply multiple criteria. SUMIF places the criteria range first, whereas SUMIFS begins with the sum range and then takes criteria-range and criterion pairs.
Why does SUMIF return zero when matching values are visible?
Common causes include extra spaces or inconsistent text, numbers stored as text, a criterion that does not match the underlying data, or misaligned ranges. Check the exact cell contents and verify the range order.
Does SUMIF include hidden rows?
Yes. Hiding rows does not ordinarily remove their values from SUMIF or SUM. If the requirement is to total only visible rows, these functions alone may not fit the task; choose a method designed for that requirement.
Related
- Excel Shortcut to Insert a Table Row on Windows and Mac
- Excel Shortcut to Insert a Row Every Other Row in a List
- Excel Shortcut to Insert a Copied Row
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- COUNTIF Excel: How to Reference Cells in CriteriaTo make COUNTIF use a cell as its criterion, put the cell reference in the second argument: with…
- Excel PV Formula for Different PaymentsExcel’s PV function is designed for a fixed payment repeated over a set number of periods, so it…
- Excel Shortcut for Removing a Filter: What WorksIn Excel for Microsoft 365 on Windows, press Ctrl+Shift+L to turn AutoFilter off or on; this removes the…
- Excel Text Handling: Formulas, Formatting, and FixesExcel text handling means entering, cleaning, combining, splitting, and displaying text without accidentally changing its meaning. In Microsoft…
- Excel SUMIFS With Values From a ListTo use Excel SUMIFS with values in a list, put the values in a criteria range and wrap…
- Box Plot Formula: Quartiles, IQR, and WhiskersA box plot has no single formula: it is built from a five-number summary—minimum, first quartile (Q1), median,…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.