SUMIFS

Excel Functions

Sum cells in a range that meet criteria

Syntax

EXCEL

=SUMIFS(sum_range, range1, criteria1, [range2], [criteria2], ...)

Arguments

Parameter	Description
`sum_range`	The range to be summed.
`range1`	The first range to evaluate.
`criteria1`	The criteria to use on range1.
`range2`	[optional] The second range to evaluate.
`criteria2`	[optional] The criteria to use on range2.

Return Value

The sum of cells that meet all criteria

Details

The SUMIFS function sums numbers in Excel when they meet one or more specific conditions. SUMIFS is one of Excel's most widely used functions, and you will see it in all kinds of spreadsheets that calculate conditional sums based on dates, text, or numbers. Although a common function, SUMIFS has a unique syntax that splits logical conditions into two parts, making it different from many other Excel functions. As a result, the task of defining criteria in SUMIFS can be a bit tricky. Also note that SUMIFS uses "AND logic". To be included in the final result, all conditions must be TRUE. The syntax for the SUMIFS function depends on the criteria being evaluated. Each condition is provided with a separate range and criteria. The generic syntax for SUMIFS looks like this: Note that the sum_rang

Examples

Syntax

The syntax for the SUMIFS function depends on the criteria being evaluated. Each condition is provided with a separate range and criteria. The generic

EXCEL

=SUMIFS(sum_range,range1,criteria1) // 1 condition
=SUMIFS(sum_range,range1,criteria1,range2,criteria2) // 2 conditions

Basic Example

The three formulas in I5:I7 look like this:

EXCEL

=SUMIFS(F5:F16,C5:C16,"red") // Red
=SUMIFS(F5:F16,C5:C16,"red",D5:D16,"TX") // Red and TX
=SUMIFS(F5:F16,C5:C16,"red",D5:D16,"TX",F5:F16,">20") // Red and TX and >20

Criteria in another cell

It is often convenient to put criteria in another cell and then refer to this cell inside your formula. This makes it easy to change criteria later wi

EXCEL

=SUMIFS(sum_range,range,A1)

Criteria in another cell

When a condition requires an operator, you must concatenate the cell reference to the operator. For example, to sum cells in a range greater than A1,

EXCEL

=SUMIFS(sum_range,range,">"&A1)

Criteria in another cell

Note we are joining the ">" operator to cell A1 with an ampersand (&) character. In the worksheet below, SUMIFS has been configured to return the sum

EXCEL

=SUMIFS(D5:D9,D5:D9,">"&G4) // sum if greater than G4

Not equal to

EXCEL

=SUMIFS(C5:C9,B5:B9,"<>red") // not red
=SUMIFS(C5:C9,B5:B9,"<>blue") // not blue
=SUMIFS(C5:C9,B5:B9,"<>"&E7) // not E7

SUMIFS

Syntax

Arguments

Return Value

Details

Examples

See Also