SUMIF

Add up numbers in a range that meet a specific condition with SUMIF

Math & Trigonometry
|
Excel All versions
|
Google Sheets Supported

Spreadsheet editor

Syntax

=SUMIF(range, criteria, [sum_range]) Returns: Number

Arguments

Argument Required Description
range Yes The range of cells that you want to apply the criteria to.
criteria Yes The condition or criteria that determines which cells to add.
sum_range No The actual cells to sum if you want to apply the criteria to a different range than the range being evaluated.

About

SUMIF adds up numbers when they meet a condition you specify. You provide the range to check, the condition to test, and optionally a separate range to sum. This makes it simple to calculate totals like sales above a target, expenses in a category, or hours worked by a specific person.

Use SUMIF when you need conditional totals but don't want to manually filter your data first. It's faster than sorting through rows yourself and updates automatically when your data changes. For more complex scenarios with multiple conditions, check out SUMIFS. If you need to count instead of sum, try COUNTIF.

SUMIF works with numbers, text, and dates. You can use comparison operators (>, <, >=, <=, <>) in your criteria, and wildcards (* and ?) for partial text matches. If you skip the sum_range argument, Excel sums the cells in the range argument that meet your criteria.

Examples

Separating income from expenses

See how SUMIF handles a mix of positive and negative numbers in this transaction log. Change the amounts and watch the income (">0") and expense ("<0") totals update on their own.

Spreadsheet editor

Mismatched range pitfall

Watch what happens when your range and sum_range don't line up. SUMIF keeps the shape of range but starts from the first cell of sum_range, so the off-by-one version quietly sums the wrong rows. Compare the two results.

Spreadsheet editor

Matching categories with wildcards

Use the * wildcard to total every label that starts the same way. Edit the criteria to "Travel*" or add new Office rows and see SUMIF pick them up without an exact name match.

Spreadsheet editor

Watch out for

⚠

Mismatched range sizes

If your sum_range is a different size than your range argument, Excel sums starting from the first cell in sum_range using the same dimensions as range. This can lead to summing the wrong cells.

→ Always make sure range and sum_range have the same number of rows and columns. If you're checking A2:A100, your sum_range should also span 99 cells.

⚠

Text criteria without quotes

Using text criteria like =SUMIF(A1:A10, Sales, B1:B10) without quotes around "Sales" causes Excel to look for a cell named Sales instead of the literal text.

→ Wrap text criteria in double quotes: =SUMIF(A1:A10, "Sales", B1:B10). This also applies to comparison operators with numbers like ">100".

⚠

Long text strings

SUMIF returns incorrect results when matching text longer than 255 characters, or when trying to match the error value #VALUE!.

→ Keep criteria text under 255 characters. For longer text comparisons, consider using helper columns to create shorter identifier codes.

⚠

Case sensitivity confusion

SUMIF treats "sales", "Sales", and "SALES" as identical, which might not match your expectations if you're tracking case-sensitive product codes.

→ SUMIF is always case-insensitive. If you need case-sensitive matching, you'll need to use a different approach with SUMPRODUCT and EXACT.

Tips & notes

When you omit the sum_range argument, SUMIF sums the cells in range that meet your criteria. This is handy when the values you're checking and summing are in the same column. Wildcards only work with text: use * to match any sequence of characters, and ? to match a single character. To find a literal asterisk or question mark, type ~* or ~?.

Common questions

How do I use comparison operators in SUMIF criteria?

Put the operator and number in quotes as a text string. For example: =SUMIF(A1:A10, ">100", B1:B10) sums values in B where A is greater than 100. You can use >, <, >=, <=, <> (not equal), or = (equal).

Can I use cell references in my criteria?

Yes, you can reference cells for dynamic criteria. Use concatenation with the operator: =SUMIF(A1:A10, ">"&D1, B1:B10) where D1 contains your threshold number. For text matching, just use the cell reference directly: =SUMIF(A1:A10, D1, B1:B10).

What's the difference between SUMIF and SUMIFS?

SUMIF handles one condition, while SUMIFS handles multiple conditions. SUMIFS also has a different argument order (sum_range comes first). Use SUMIFS when you need AND logic like summing sales for a specific product AND region.

Why is my SUMIF returning zero when I know there are matches?

Check for extra spaces in your data or criteria, mismatched text formatting (dates stored as text), or incorrect cell references. Use TRIM to clean up spaces, and verify your range and sum_range cover the right cells.