ExcelUser logo Free guides and templates
NOTICE. While work is in progress, much of this topic is incomplete. You can get more information here. Thanks for your patience.--Charley Kyd
Home >  Excel Help >  Excel Functions >  

Math and trigonometry

SUMIF Function

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


Charley Kyd is a Microsoft Excel MVP by Charley Kyd, MBA
Microsoft Excel MVP

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

If you're a subscriber to my Excel for Business newsletter, you can download this example workbook here. Otherwise, you can subscribe here for free, and then download this workbook.

Example 1:

SUMIF function, Example 1

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

Example 2:

SUMIF function, 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 function, Example 3

SUMIF returns the total for Hats in the Sum_Range.

Example 4:

SUMIF function, Example 4

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

Example 5:

SUMIF function, 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 function, 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 function, Example 7

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

Example 8:

SUMIF function, Example 8

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

Example 9:

SUMIF function, 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:

SUMIF function, 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 function, 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 function, 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 function, 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

 

Dashboard Reporting With Excel


IncSight DB Excel Dashboards


Charley's SwipeFile charts