SUMIFS Function
Adds numbers that meet all of the specified conditions.
Syntax
Parameters
| Parameter | Description | Required |
|---|---|---|
| sum_range | The range of cells to sum. | Required |
| criteria_range1 | The range to evaluate with the first criteria. | Required |
| criteria1 | The condition that criteria_range1 must satisfy. | Required |
| criteria_range2, criteria2 | Additional range/criteria pairs (optional). | Optional |
Basic Example
Sum sales in the East region
=SUMIFS(B2:B100, A2:A100, "East")Adds values from B2:B100 only where A2:A100 equals "East".
Advanced Examples
Example 1: Multiple criteria
Sales by region and productSum sales where region is East and product is Widget
=SUMIFS(C2:C100, A2:A100, "East", B2:B100, "Widget")Example 2: Date range criteria
Monthly totalsSum sales in January 2024
=SUMIFS(B2:B100, A2:A100, ">=2024-01-01", A2:A100, "<=2024-01-31")How SUMIFS Works
SUMIFS iterates through each row in the criteria ranges. A row is included only if every criterion is met. Then it sums the corresponding values from sum_range.
Important Notes & Limitations
All criteria ranges must be the same size and shape as sum_range.
Criteria are combined with AND logic; every condition must be true.
To sum if any condition is true (OR logic), use multiple SUMIF or SUM with addition.
Criteria cannot reference a cell outside the range.
Common Errors & Fixes
#VALUE! errorCriteria ranges are not the same size as sum_range.Fix: Ensure all ranges have the same number of rows and columns.
Wrong sumCriteria string includes operators incorrectly, e.g. ">1000" without quotes.Fix: Use quotes for text and operators: ">1000" or cell reference.
0 resultNo rows match all criteria.Fix: Check criteria spelling and data types.
Download Practice File
SUMIFS
sumifsPractice SUMIFS with Real Data
Download a sample CSV file with pre-populated data and practice exercises for the SUMIFS function. Works in both Excel and Google Sheets.
File format: CSV (comma-separated values) - opens in Excel, Google Sheets, and all spreadsheet apps
Related Tutorials
AVERAGEIFS Multiple Criteria
AVERAGEIFS multiple criteria lets you average cells that satisfy two or more conditions at once. Learn the syntax, real examples, and how it differs from AVERAGEIF and SUMIFS.
Excel Expense Tracker
Build an Excel expense tracker from scratch: log spending on one sheet, then use SUMIF, SUMIFS, SUM and TODAY for category totals and month-to-date spend.
Excel Sales Dashboard: Build One That Updates Itself
Build an Excel sales dashboard that keeps itself current: one flat sales table, SUMIFS KPIs, XLOOKUP targets and FILTER drill-downs that grow with the data.
INDEX MATCH with Multiple Criteria
INDEX MATCH with multiple criteria lets you match on two or more columns in Excel and Google Sheets. Learn the exact array formula plus a cleaner XLOOKUP alternative.
Sum Only Visible Cells Filtered
Learn how to sum only visible cells filtered data with SUBTOTAL codes 9 and 109, why a plain SUM keeps hidden rows, and the SUMPRODUCT criteria trick.
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.
SUMPRODUCT with Multiple Conditions
Need more conditions than SUMIFS handles? Learn how SUMPRODUCT with multiple conditions solves AND, OR, case-sensitive and partial-text sums in Excel.
VLOOKUP with Multiple Criteria
VLOOKUP only matches one column, but real lookups often need two or more. Learn four reliable ways to do a VLOOKUP with multiple criteria in Excel and Google Sheets.