|
Classic file types are no longer available for use as of January 2021. You can transition your classic files or download a PDF. Learn More

Workiva Support Center

SUMIFS

SUMIFS

  • Last updated on

Description 

 Use this function to return the sum of cells that meet a set of multiple conditions.

 

Syntax

SUMIFS(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 criteria_1.

criteria_range_1 and criteria_1 set up a search pair where the range is searched for the specified criteria. Once items in the range are found, their corresponding values in range are added.

 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:

  • SUMIFS(: This is the function itself.
  • E2:E17: The sum_range.
  • B2:B17: The first criteria_range.
  • "red": The first criteria.
  • The formula sums all cells in E2:E17 where the corresponding cell in B2:B17 is "red".

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:

  • SUMIFS(: This is the function itself.
  • E2:E17: The sum_range.
  • B2:B17: The first criteria_range.
  • "red": The first criteria (for B2:B17).
  • C2:C17: The second criteria_range.
  • "TX": The second criteria (for C2:C17).
  • The formula sums cells in E2:E17 only where the corresponding cell in B is "red" AND the cell in C is "TX".

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:

  • SUMIFS(: This is the function itself.
  • E2:E16: The sum_range.
  • D2:D16: The criteria_range.
  • ">3": The criteria (greater than 3).
  • The formula sums cells in E2:E16 where the corresponding cell in D2:D16 is greater than 3.

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:

  • SUMIFS(: This is the function itself.
  • E2:E17: The sum_range (16 cells).
  • B2:B10: The criteria_range (9 cells).
  • The SUMIFS function requires that the sum_range and all criteria_range arguments have the exact same size.
  • Because the ranges are different sizes (16 vs 9), the formula cannot be evaluated.

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.;

 

Related functions

Description  Use this function to return the sum of cells that meet a set of multiple conditions. SyntaxSUMIFS(range,...