ExcelMathIntermediate

SUMIF

Adds the cells specified by a given condition.

Read the syntaxReview worked examplesOpen the spreadsheet app
=SUMIF(range, criteria, [sum_range])
Open Spreadsheet Explorer

This static page keeps the function indexed for search, while the spreadsheet app handles interactive exploration and saved formulas.

What the function does

Overview

Adds the cells specified by a given condition.

The SUMIF function performs a conditional sum by evaluating a single range against a specific criterion. It supports a wide array of criteria including logical operators (>, <, <>, =), wildcards (*, ?), and direct cell references. For best practices, ensure the 'range' and 'ange' are of identical size and shape. If the 'ange' is omitted, Excel sums the values in the 'range' argument itself. A critical edge case involves mismatched range sizes: Excel does not return an error but instead dynamically determines the sum area starting from the top-left cell of the 'ange', which often leads to inaccurate totals. Additionally, text criteria and logical operators must be enclosed in double quotes. For multiple criteria, users should transition to the SUMIFS function, which is optimized for multi-conditional logic.

Quick reference

Syntax

=SUMIF(range, criteria, [sum_range])

Inputs

Arguments

rangeThe range of cells to check criteria against
Example: A2:A100
criteriaThe condition (number, expression, or text)
Example: ">1000"
sum_range (optional)The cells to add (if different from range)
Example: B2:B100

Formula patterns

Examples

1

Sum values based on a text category

=SUMIF(A2:A10, "Furniture", C2:C10)
text matchaccounting
2

Sum values greater than a numeric threshold

=SUMIF(B2:B20, ">500")
comparisonmath
3

Sum values using partial match wildcards

=SUMIF(A2:A15, "*Total", B2:B15)
wildcardssearch

Avoid these issues

Common Errors

1

#VALUE!

Cause: The criteria string is longer than 255 characters or the formula refers to a closed external workbook.

Fix: Shorten the criteria string or ensure the referenced workbook is currently open.

2

Misaligned Ranges

Cause: The 'range' and 'sum_range' arguments have different dimensions (e.g., A2:A10 and B2:B20).

Fix: Modify the range references so they represent the same number of rows and columns (e.g., A2:A20 and B2:B20).

Platform support

Compatibility

Excel 2007+Excel-first

Source: Microsoft Support

Common questions

Frequently Asked Questions

Adds the cells specified by a given condition.

range: The range of cells to check criteria against criteria: The condition (number, expression, or text) ange: The cells to add (if different from range)

#VALUE!: Shorten the criteria string or ensure the referenced workbook is currently open. Misaligned Ranges: Modify the range references so they represent the same number of rows and columns (e.g., A2:A20 and B2:B20).