SUMIF

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

Math & Trigonometry
|
Excel All versions
|
Google Sheets Supported

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

Use SUMIF to separate positive values from negative values in a mixed list. See how comparison operators like ">0" and "<0" let you total income separately from expenses in a transaction log.

Spreadsheet editor

Mismatched range pitfall

See what happens when your range and sum_range don't align perfectly. This demonstrates the common mistake where Excel uses the dimensions of range but starts from sum_range's first cell, potentially summing the wrong data.

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.

Practice this function

Bank statement reconciliation

Advanced

Reconcile a bank statement with internal records, identify missing items.

Start →