The SUMIF and SUMIFS functions are the most direct tools to sum numbers subject to conditions in Excel. They are used whenever you need to answer questions like “how much is the revenue for this region?” or “what is the sum of sales between these dates for this product?”. In this article, I break down the syntax, show concrete examples, anticipate common pitfalls, and offer alternatives when needs exceed what these formulas naturally provide.
Somaire
In brief
🧾 SUMIF sums based on a single criterion: useful to filter a range (text, number, dates). Keep in mind the difference in argument order between SUMIF and SUMIFS.
🔗 SUMIFS handles multiple criteria simultaneously (and all must be true). Ideal for crossing product, period, and geographic area without a pivot table.
⚠️ Common errors come from misuse of ranges (different sizes), quotes around text criteria, and wildcards “*” / “?”. Also check cell formats after importing a CSV file.
🚀 For more complex calculations or when performance becomes an issue, turn to SUMPRODUCT, calculated columns, or query tools if the workbook grows.
Understanding syntax and logic
SUMIF: the simple case (a single criterion)
The SUMIF formula is used when you have a range to check and, optionally, a separate sum range. In French, the syntax is:
- =SUMIF(range; criterion; [sum_range])
Explanation: range is the area where Excel looks for the criterion (e.g. “Product” column), criterion defines what you are looking for (e.g. “Apples” or “>100”), and sum_range (optional) is the area whose values are summed; if omitted, Excel sums the range itself if it contains numbers.
Concrete examples
Suppose a table with columns A=Product, B=Quantity, C=Amount. To get the total amount sold for “Apples”:
- =SUMIF(A2:A100,”Apples”,C2:C100)
You can use operators in the criterion: =SUMIF(B2:B100,”>=10″,C2:C100) will sum the amounts where the quantity is at least 10.
SUMIFS: multiple criteria, AND logic
Syntax and particularities
To cross multiple conditions, SUMIFS is the answer: it requires that all range/criterion pairs be met. Its syntax is:
- =SUMIFS(sum_range, criteria_range1, criterion1, [criteria_range2, criterion2], …)
Note the difference in order with SUMIF: sum_range comes first. This is a frequent source of errors when mentally transposing one to the other.
Practical examples
Sum of amounts for “Apples” sold between 01/01/2025 and 03/31/2025:
- =SUMIFS(C2:C100, A2:A100, “Apples”, D2:D100, “>=”&DATE(2025,1,1), D2:D100, “<=”&DATE(2025,3,31))
Here you see the use of concatenation (&) to join an operator with a date or a number. For partial text criteria, add wildcards: “*string*” for a match containing the string.
Practical comparison
| Function | When to use it | Limit |
|---|---|---|
| SUMIF | Single criterion; quick for simple filters | Cannot naturally chain multiple criteria (without nesting or multiplying functions) |
| SUMIFS | Multiple criteria with AND logic | Does not directly handle OR logic; reduced performance on very large tables |
Traps and best practices
1. Ranges of the same size
The number one cause of #VALUE! error is the different size between sum_range and the criteria_ranges. Always check that each range starts and ends on the same row. A little tip: use named ranges or structured tables to avoid these offsets.
2. Text criteria and quotes
For any criterion that is not a direct cell reference, surround it with quotes: “France”, “>500”. If the criterion comes from a cell (e.g. E1), write “>”&E1 to link the operator to the content.
3. Wildcards and partial matches
The wildcards * and ? work in text criteria: “*key*” finds any cell containing “key”. Note: if you are looking for a literal * or ? character, you must use the tilde (~) before it.
4. Hidden formats after import
When your numbers do not add up, it often comes from a text format. After importing a CSV file, check the formats: use Paste Special > Values, or convert via Data > Text to Columns.
Advanced cases and alternative solutions
OR logic (multiple possible values)
SUMIFS is based on AND logic. If you want to sum over multiple possible values (e.g. products A or B), you can:
- Sum several SUMIFs (e.g. =SUMIF(…, “A”, …) + SUMIF(…, “B”, …)).
- Use SUMPRODUCT for more compact and sometimes more efficient formulas: =SUMPRODUCT((A2:A100={“A”;”B”})*(C2:C100)).
Performance on very large datasets
Multiple SUMIFS executed on millions of rows can slow down Excel. The following approaches reduce the cost:
- Convert the range to a structured table (Ctrl+T) to benefit from structured references.
- Use helper columns to calculate Boolean indicators and then sum only one column.
- If relevant, load the data into Power Query to perform aggregations upstream.
Detailed practical examples
Imagine a sales table:
| Product | Date | Amount | Region |
|---|---|---|---|
| Apple | 2025-01-05 | 120 | North |
| Pear | 2025-02-10 | 80 | South |
| Apple | 2025-03-12 | 60 | North |
To get the total amounts for “Apple” in the “North” region:
- =SUMIFS(C2:C100, A2:A100, “Apple”, D2:D100, “North”)
If you have a list of products to exclude, first calculate a helper column indicating whether the row should be included, then sum this marker multiplied by the amount.
Useful Additions
Conditional counting functions like COUNTIF and COUNTIFS often complement SUMIF and SUMIFS: you count before summing to validate filters or diagnose inconsistencies. You will find practical methods for conditional counting by exploring COUNTIF and COUNTIFS in dedicated guides on the same site, especially to check the consistency of criteria without launching heavy aggregations.
FAQ
What to do if SUMIFS returns 0 even though values exist?
First check the ranges (same size), make sure the criteria match the cell format (text vs number), and test each criterion independently with COUNTIF to identify which one filters everything out. Another common cause: invisible spaces. Use =TRIM(cell) to clean.
Can regular expressions be used in criteria?
No, Excel does not integrate regular expressions in SUMIFS. The wildcards * and ? suffice for most needs. For complex patterns, consider Power Query or VBA.
How to properly handle OR logic in a single formula?
The standard technique is to sum multiple SUMIFs or use SUMPRODUCT with criteria arrays. Example: =SUMPRODUCT(((A2:A100=”A”)+(A2:A100=”B”))*(C2:C100)).
What is the difference between SUMIFS and Power Query functions?
SUMIFS calculates in the worksheet and is ideal for quick queries. Power Query transforms and aggregates data upstream: it is more robust for recurring processing on large sources, but requires refreshing and a learning step.
Resources and Final Recommendations
Before deploying formulas on a large workbook, test them on a sample and document your choices (auxiliary columns, named ranges). To avoid errors after import, perform data quality control — you can also automate import correction via Power Query. Finally, if you manage complex reports, consider pivot tables for quick summaries, and keep SUMIFS for integrated and dynamic calculations within cells.
{
“@context”: “https://schema.org”,
“@type”: “WebPage”,
“about”: {
“@type”: “Thing”,
“name”: “SUMIF and SUMIFS in Excel”
},
“keywords”: [“SUMIF”, “SUMIFS”, “SUMIF”, “criteria”, “Excel”]
}
