Sum (add) values in a range that meet multiple specific criteria.

SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...)

---

SUMIFS (with the 's'!) allows you to add up values associated with multiple sets of criteria in a range or table. The SUMIF function by contrast can only search for a singular criterion.

The SUMIFS syntax requires the sum_range to be added, the criteria_ranges to be searched and the criteria to be applied.

SUMIFS can be used with dates, numbers, and text and supports logical operators (>,<,<>,=) and wildcards (*,?) for partial matching.

---

SUMIFS with text and numbers.

In the examples below, the data range shows a list of countries, the region they belong to and their respective population. SUMIFS is used to determine total population sizes using multiple criteria such as region name and population size ranges.

---

SUMIFS and dates.

In the examples below, SUMIFS is used to determine total sales for given dates or date ranges. Dates can be expressed as stated values ("1-Mar-22") or as cell references or using the DATE function (=DATE(2022,3,1)).

---

Syntax

The SUMIFS function consists of at least 3 arguments: =SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...)

1. sum_range. Required. The range of cells to sum.
2. criteria_range1. Required. The first criteria range to be evaluated.
3. criteria1. Required. The first set criterion to evaluate against.
4. criteria_range2. Optional. The second (and subsequent) criteria range to be evaluated.
5. criteria2. Optional. The second (and subsequent) set criterion to evaluate against.

Try it now!

• Enter your formulas into the grey cells.

---

Scope

SUMIFS can be applied to ranges, named ranges and tables. It can also be used with logical conditions, wildcards and cell references.

Range:	=SUMIFS(D3:D12,C3:C12,"*Asia",B3:B12,"<>China") // region is anything with "Asia" in the name (* is a wildcard) and country must exclude "China"
Named Range:	=SUMIFS(Population,Region,"*Asia",Country,"<>China") // sum_range and criteria_range are defined by Named Ranges.
Table:	=SUMIFS(Table1[Population],Table1[Region],"*Asia",Table1[Country],"<>China") // sum_range and criteria_range are defined by table columns.
Logical Condition:	=SUMIFS(C3:C12,B3:B12,">="&C14,B3:B12,"<="&C15) // greater than or equal to the contents of cell C14 and less than or equal to the contents of cell C15
Wildcard:	=SUMIFS(Population,Region,"*Asia",Country,"?????") // region can be anything with "Asia" in it (e.g. South Asia, East Asia) and country name must contain 5 characters (e.g. China, India, Japan).

Caveats

SUMIFS function:

• can handle up to 127 range / criteria pairs.
• multiple conditions are applied using AND logic; i.e. criteria1 AND criteria2.
• text strings in criteria need to be in quotations. Cell references do not need to be in quotations.
• TRUE or FALSE values in sum_range are evaluated to 1 and 0 respectively.
• use the ? and * characters for wildcards. To find an actual question mark or asterisk, type a tilde (~) preceding the character.
• sum_range and criteria_range should be the same size.
• logical operators (<,>,<>,=) can be used for partial matches and should be enclosed in double quotation marks "" along with non-numeric criteria being evaluated (quotation marks are not required for numeric values).
• cell references in criteria are not enclosed in quotes, i.e. "<"&A1
• is not case sensitive.

---

Common Issues

• The order of arguments is different to SUMIF. The sum_range comes first, followed by the criteria_ranges and criteria.
• sum_range and criteria_range should be the same size.
• #VALUE! is returned if sum_range and criteria_range have different sizes.

---

Related Functions

• SUMIF: sum (add) values in a range that meet a singular criteria.
• COUNTIF: count values within a range that meet one specific criteria.
• COUNTIFS: count values within a range that meet specific or multiple criteria.

---

Functions Category

• Math & Trigonometry