>   >   >

# SUMIF Function

## Adds the cells in a row or column, as specified by one criteria

 by Charley Kyd, MBAMicrosoft Excel MVP, 2005-2014 The Father of Spreadsheet Dashboard Reports

SUMIF summarizes data in a range using one criteria.

Syntax

SUMIF(range, criteria, sum_range)

• range  Required. The range of cells for the criteria to evaluate.

• criteria  Required. The criteria in the form of a number (like 50), text (like "sales"), or expression as text (like ">=99"). Text must be enclosed in quotation marks (like: "sales"). You also can use the wildcard characters "?" (to match a single character) and "*" (to match any number of characters).

• sum_range  Optional. The range of cells to add. If this argument is omitted, SUMIF adds values in range.

Applies To

Excel 2003 and above

Remarks

Keep in mind that SUMIF, which allows one criteria, and SUMIFS, which allows more than one criteria, arrange their arguments in a different sequence. For this reason, it's often more conventient to use the SUMIFS function for even one criteria.

Examples

You can download this example workbook here, along with all other example workbooks I've completed for this Excel help area.

Example 1:

SUMIF returns the sum of all values that are greater than 10.

Example 2:

Because no sum_range is specified, SUMIF must sum "Hats". But "Hats" isn't numeric, so its sum is zero.

Example 3:

SUMIF returns the total for Hats in the Sum_Range.

Example 4:

The case of the Criteria doesn't matter. SUMIF still returns the total for Hats in the Sum_Range.

Example 5:

SUMIF returns the sum of the Sum_Range values for Range values that meet the Criteria, whether they're entered as numbers or text.

Example 6:

SUMIF returns the sum of all numeric Codes greater than 75. Here, the Range item "88" is ignored because it's entered as text and has no numeric value.

Example 7:

SUMIF returns the sum for all Range items starting with "H" and ending with "s".

Example 8:

SUMIF returns the sum of Sum_Range values for Range items like t?es, where "?" can be any character.

Example 9:

SUMIF converts the date expressed as text in the Criteria cell to a date serial number, then returns the proper value.

Example 10:

Here, the Criteria cell isn't empty. Instead, it contains a single-quote ('). It also could contain a null string entered by formula (=""). In either case, SUMIF returns the sum of the Sum_Range values for Range cells that are empty.

Example 11:

SUMIF adjusts the Sum_Range to be in sync with the Range. Here, when the Sum_Range is only one cell, SUMIF expands it apply to the entire Range column.

Example 12:

SUMIF adjusts the Sum_Range to be in sync with the Range. Here, when the Range is only one cell, SUMIF redues the Sum_Range so that it corresponds with only that one Range cell.

Example 13:

SUMIF adjusts the Sum_Range to be in sync with the Range. When the Range is one column, SUMIF contracts the Sum_Range to be the same.

Other Help