SUMIF

Excel Functions

Sum cells in a range that meet criteria

Syntax

EXCEL

=SUMIF(range, criteria, [sum_range])

Arguments

Parameter	Description
`range`	Range to apply criteria to.
`criteria`	Criteria to apply.
`sum_range`	[optional] Range to sum. If omitted, cells in range are summed.

Return Value

The sum of matching cells

Details

The SUMIF function adds up numbers in Excel when they meet a specific condition. It's one of Excel's most widely used functions, and you will find it in all kinds of spreadsheets that calculate conditional sums based on dates, text, or numbers. While powerful, SUMIF has a unique syntax that splits logical conditions into two parts, making it different from many other Excel functions. This syntax takes a little getting used to. SUMIF can only apply one condition. For multiple conditions, use the SUMIFS function. The generic syntax for SUMIF looks like this: The criteria is applied to cells in the range. When cells meet the criteria, corresponding cells in the sum_range are summed. The sum_range is optional. If sum_range is omitted, the cells in range are summed instead. In the worksheet bel

Examples

Basic Example

The generic syntax for SUMIF looks like this:

EXCEL

=SUMIF(range,criteria,[sum_range])

Basic Example

The criteria is applied to cells in the range. When cells meet the criteria, corresponding cells in the sum_range are summed. The sum_range is optiona

EXCEL

=SUMIF(B5:B14,"jim",D5:D14) // sum sales by Jim
=SUMIF(C5:C14,"ca",D5:D14) // sum sales in CA
=SUMIF(D5:D14,">100") // sum sales over 100

Criteria in another cell

A great way to use SUMIF is to put criteria in another cell and then refer to this cell inside your formula. This makes it easy to change criteria lat

EXCEL

=SUMIF(range,A1)

Criteria in another cell

If you want to include an operator in the criteria, you will need to concatenate the cell reference to the operator. For example, to sum cells in a ra

EXCEL

=SUMIF(range,">"&A1)

Criteria in another cell

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

EXCEL

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

Not equal to

EXCEL

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

SUMIF

Syntax

Arguments

Return Value

Details

Examples

See Also