Excel offers a host of powerful functions for automating calculations, including SUM.IF. This formula enables you to add values together, provided they meet a defined criterion. It’s an essential tool for analyzing data with precision and filtering results according to your needs.
Whether you want to add up amounts associated with a specific customer, a specific date or a numerical range, SOMME.SI saves you precious time in your spreadsheets.
What is the SUM.IF function?
The SUM.SI function performs a conditional sum: it examines a range of cells, applies a criterion, then adds together only those values that meet that condition.
Basic syntax:
=SUMM.SI(criterion_range; criterion; [sum_range])
| Element | Description |
|---|---|
criterion_range | The range of cells to be analyzed according to a criterion |
criterion | The condition to be met (value, expression, text, etc.) |
range_sum | (Optional) The range containing the values to be summed |
If range_sum is omitted, Excel will sum the values in range_criterion.
Simple examples with SUM.IF
Here are a few concrete examples to help you understand how to use this function.
| Range | Criteria | Formula | Expected result |
|---|---|---|---|
| B2:B10 | ">100" | =SOMME.SI(B2:B10;">100") | Total of cells from B2 to B10 > 100 |
| A2:A10 | "Customer A" | =SUMM.SI(A2:A10; "Customer A";C2:C10) | Sum of sales for “Customer A |
| A2:A10 | ">01/01/2023" | =SOMME.SI(A2:A10;">01/01/2023";B2:B10) | Total amounts for dates after 2023 |
Define your criteria
1. Text
To filter by text, enclose the keyword in quotation marks.
=SUMM.SI(B2:B10; "Pending"; C2:C10)
This adds up the amounts from C2 to C10 when the status in B2 to B10 is “Pending”.
2. Numbers
Numerical criteria are used with comparison operators:
=SUM.SI(A2:A10; ">500")
This adds all values above 500.
3. Dates
Make sure you use the correct date format and enclose them in quotation marks.
=SUM.SI(A2:A10; ">=01/01/2022"; B2:B10)
4. Wildcards
Wildcards * (multiple characters) and ? (single character) can partially filter.
=SUM.SI(C2:C10; "A*Z"; B2:B10)
This adds up the amounts if the customer’s name begins with “A” and ends with “Z”.
Going further: SUM.SI.ENS for multiple criteria
When a single criterion isn’t enough, use SUM.SI.ENS to add up according to multiple conditions.
Syntax:
=SUMM.SI.ENS(range_sum; range_criterion1; criterion1; range_criterion2; criterion2; ...)
Example 1 :
Add up sales of a specific product for the month of January :
=SUMM.SI.ENS(C2:C10; A2:A10; "Product A"; B2:B10; "01/2023")
Example 2:
Add up the hours worked for an employee on a project :
=SOMM.SI.ENS(D2:D20; A2:A20; "Employee 1"; B2:B20; "Project X")
Integration with other functions
SUM.SI + NB.SI
Combine SUM.SI with NB .SI to obtain the sum and number of occurrences:
=SUMM.SI(B2:B20; "Customer B")
=NB.SI(B2:B20; "Customer B")
SUM.SI + SI
You can insert SUM.SI into a logic function:
=SI(SUM.SI(C2:C10;">500") > 3000; "Budget exceeded"; "Budget OK")
Creating summary tables with SUM.SI
Here’s an example of its use in a sales table:
| Product | Amount | Month |
|---|---|---|
| Product A | 500 | 01/2023 |
| Product B | 300 | 01/2023 |
| Product A | 200 | 02/2023 |
Formula for sum of sales of “Product A” :
=SUMM.SI(A2:A4; "Product A"; B2:B4)
Result: 700
Common errors to avoid
| Problem | Explanation |
|---|---|
| Ranges of different sizes | Ranges must have the same number of lines |
| Omission of quotation marks | Text and numeric criteria must be entered correctly |
| Missing or empty values | Check data ranges to avoid #VALUE errors! |
| Incorrect syntax | Check formula structure (semicolon, quotation marks, etc.) |