Description
Use this function to return the sum of cells that meet a set of multiple conditions.
Syntax
SUMIF(range, criteria_range_1, criteria_1, […, [criteria_range_127, criteria_127])
Inputs
This function accepts the following arguments:
| Name | Required | Description | Valid input |
|---|---|---|---|
range |
Yes | The range of cells to be summed. | A cell, a cell range, or a formula which results in either of these. |
criteria_range_1 |
Yes |
The range to be evaluated using
|
A cell, a cell range, or a formula which results in either of these. |
criteria_1 |
Yes | The criteria that defines which cells in criteria_range_1 will be added. |
A number, a cell, a cell range, or a formula which results in any of these. |
criteria_range_n |
No | Additional range(s). Up to 127 additional criteria_range values can be included. |
A cell, a cell range, or a formula which results in either of these. |
criteria_n |
No | Criteria for evaluating the associated range. Up to 127 additional criteria values can be included. |
A number, a cell, a cell range, or a formula which results in any of these. |
Examples
Sample data
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Date | Color | State | Quantity | Value |
| 2 | 2-Jan | Red | TX | 4 | $23.75 |
| 3 | 18-Feb | Blue | OH | 3 | $27.80 |
| 4 | 28-Feb | Blue | NJ | 2 | $31.20 |
| 5 | 15-Mar | Green | TX | 3 | $45.23 |
| 6 | 13-Apr | Red | CO | 4 | $58.60 |
| 7 | 30-Apr | Red | IL | 2 | $55.10 |
| 8 | 20-May | Red | AZ | 1 | $48.90 |
| 9 | 14-Jun | Green | WA | 2 | $52.30 |
| 10 | 22-Jul | Red | NY | 4 | $62.75 |
| 11 | 11-Aug | Green | TX | 4 | $41.65 |
| 12 | 19-Aug | Green | OR | 1 | $43.70 |
| 13 | 3-Sep | Green | FL | 1 | $33.50 |
| 14 | 6-Oct | Green | MA | 3 | $39.15 |
| 15 | 25-Oct | Blue | GA | 1 | $29.99 |
| 16 | 7-Nov | Blue | WA | 2 | $18.99 |
| 17 | 9-Dec | Red | TX | 3 | $37.42 |
Sample formulas
| Use case | Formula | Explanation and Result |
|---|---|---|
| Sum values in a range based on a single criterion in another range. |
=SUMIFS(E2:E17, B2:B17, "red")
|
This formula works in the following manner:
For this data set, the formula returns $286.52. |
| Sum values in a range based on multiple criteria. |
=SUMIFS(E2:E17, B2:B17, "red", C2:C17, "TX")
|
This formula works in the following manner:
For this data set, the formula returns $61.17. |
| Sum values in a range based on a numeric criterion. |
=SUMIFS(E2:E16, D2:D16, ">3")
|
This formula works in the following manner:
For this data set, the formula returns $186.75. |
| Handle mismatched range sizes in a formula. |
=SUMIFS(E2:E17, B2:B10, "blue")
|
This formula works in the following manner:
For this data set, the formula returns #VALUE!. |
Notes
- The SUMIF function supports logical operators (>,<,<>,=) and wildcards (*,?) for partial matching. Criteria should include logical operators (>,<,<>,<=,>=) as needed.
- Criteria can be applied to dates, numbers, and text.
- Each individual condition must be defined by its own range-criteria pair.
- SUMIFS supports up to 127 range-criteria pairs.
- The SUMIFS function requires actual ranges for all range arguments.
- All ranges must be the same size or SUMIFS will return a #VALUE! error.
- SUMIFS can calculate sums based on cells that are blank ("") or not blank ("<>").
- Conditions in SUMIFS are joined by AND logic. To be included in the final result, all conditions must evaluate to be TRUE.
- If text is included by a cell or range reference, then it is treated as a zero. If text is included without quotation marks, the function returns a #NAME! error. If text is included with quotation marks, the function returns a #VALUE! error.
- If arguments contain errors, SUM will return an error.
- SUMIFS isn't case-sensitive.
- SUMIFS supports wildcards.
Tips
TBD