fx
FunctionPedia
Quick Answer

SUMIF Function

SUMIF adds values that match a condition. Give it a range to check, a condition to test, and a range of numbers to sum — it only adds the ones that pass the test.

✓ Excel✓ Google SheetsExcel All versions

Syntax

SUMIF
(range, criteria, [sum_range])

Parameters

ParameterDescriptionRequired
rangeThe range of cells to evaluate with the criteria. Each cell is tested against the condition.Required
criteriaThe condition that determines which cells to sum. Can be a number, text, comparison (>100, "<>0"), or wildcard (*apples*).Required
sum_rangeThe actual cells to sum. If omitted, the 'range' itself is summed (must contain numbers).Optional

Basic Example

Sum all sales from the 'East' region

=SUMIF(A2:A50, "East", B2:B50)
Result$12,500

Column A contains region names. The formula checks each cell for 'East' and sums the corresponding sales values from column B.

Advanced Examples

Example 1: Sum values greater than a threshold

Budgeting

Sum all expenses that exceed $500

=SUMIF(B2:B100, ">500")
Result: $4,200
When sum_range is omitted, SUMIF sums the range itself. Only values >500 in B2:B100 are added.

Example 2: SUMIF with wildcard for partial match

Sales analysis

Sum sales for all products containing 'phone' in the name

=SUMIF(A2:A100, "*phone*", B2:B100)
Result: $28,750
The asterisk wildcard matches any characters. '*phone*' catches 'Smartphone', 'Phone case', 'Cellphone accessories'.

Example 3: SUMIF with date criteria

Reporting

Sum transactions that occurred after a specific date

=SUMIF(A2:A200, ">"&DATE(2024,1,1), B2:B200)
Result: $15,300
Concatenate the comparison operator with a DATE function to create a date-based criteria.

How SUMIF Works

SUMIF works in two phases. First, it scans the 'range' cell by cell, checking each against your criteria. For text criteria, it does an exact match (case-insensitive) unless you use wildcards. For number criteria with operators (>100, <=50), it evaluates the comparison. Second, for cells that pass the test, it takes the corresponding cell from 'sum_range' (or from 'range' itself if sum_range is omitted) and adds them all together.

1
Select the criteria range
Choose the column or range containing the values you want to test (e.g., the region column).
2
Define your criteria
Specify what to match — exact text like "East", a comparison like ">500", or a wildcard like "*phone*".
3
Select the sum range
Pick the column of numbers to add up. If omitted, the criteria range itself is summed.
4
Enter the formula
Type =SUMIF(criteria_range, condition, sum_range) and press Enter.

Important Notes & Limitations

  • SUMIF handles only one condition. For multiple conditions (e.g., region='East' AND year=2024), use SUMIFS.

  • Text criteria are case-insensitive — 'east' and 'East' match the same cells.

  • SUMIF doesn't work well with 2D ranges — both range and sum_range should be 1D (single row or column).

  • Criteria must be a string, number, or cell reference — you can't pass a complex logical expression directly.

Common Errors & Fixes

0 result (wrong sum_range size)sum_range is a different size than range — mismatched dimensions

Fix: Make sure sum_range has the same number of rows/columns as range. If range is A2:A50, sum_range should be B2:B50 (not B1:B49).

0 result (numbers stored as text)The sum_range contains numbers formatted as text, which SUMIF ignores

Fix: Convert text numbers to actual numbers using VALUE() or reformatting the cells as Number.

Wrong result (criteria text mismatch)Trailing spaces or different formatting in the criteria range

Fix: Use TRIM() to clean data, or use wildcards like "*East*" to handle minor differences.

Download Practice File

Practice SUMIF with Real Data

Download a sample CSV file with pre-populated data and practice exercises for the SUMIF function. Works in both Excel and Google Sheets.

Works in Google SheetsCompatible with ExcelIncludes exercises

File format: CSV (comma-separated values) - opens in Excel, Google Sheets, and all spreadsheet apps

Related Tutorials

AVERAGEIF with Criteria

Learn how to use AVERAGEIF with criteria in Excel and Google Sheets: syntax, text, number and date conditions, wildcards, and when to switch to AVERAGEIFS.

Excel Budget Template

Build an Excel budget template from scratch: structure your sheets, total spending by category with SUMIF, flag overspending with IF, and track averages.

SUMIF with a Date Range

SUMIF only takes one condition, so summing between two dates needs a trick. Learn three clean ways to SUMIF (or SUMIFS) over a date range in Excel and Google Sheets.

SUMIFS Multiple Criteria

Build SUMIFS multiple criteria formulas that total values matching two or more conditions in Excel and Google Sheets, including dates, wildcards and OR logic.

Frequently Asked Questions

What's the difference between SUMIF and SUMIFS?
SUMIF handles one condition. SUMIFS handles multiple conditions and has a different argument order: =SUMIFS(sum_range, criteria_range1, criteria1, criteria_range2, criteria2, ...). The sum_range comes first in SUMIFS.
Can SUMIF use cell references for criteria?
Yes. =SUMIF(A2:A50, D1, B2:B50) uses the value in cell D1 as the criteria. Combine with operators: =SUMIF(A2:A50, ">"&D1, B2:B50).
Why does SUMIF return 0?
Common causes: (1) numbers stored as text in sum_range, (2) mismatched range sizes, (3) criteria not matching any cells. Check data formatting first.
Can SUMIF sum based on color?
No. SUMIF cannot filter by cell color or font color. You'd need a VBA macro or manual filtering for color-based summing.
How do I sum with two conditions?
Use SUMIFS instead: =SUMIFS(B2:B50, A2:A50, "East", C2:C50, "2024") sums column B where A is 'East' AND C is '2024'.