Quick Answer

SUMIFS Function

Adds numbers that meet all of the specified conditions.

Syntax

SUMIFS

(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...)

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")

ResultTotal East sales

Adds values from B2:B100 only where A2:A100 equals "East".

Advanced Examples

Example 1: Multiple criteria

Sales by region and product

Sum sales where region is East and product is Widget

=SUMIFS(C2:C100, A2:A100, "East", B2:B100, "Widget")

Result: Widget sales in East

Both conditions must be true for the row to be included.

Example 2: Date range criteria

Monthly totals

Sum sales in January 2024

=SUMIFS(B2:B100, A2:A100, ">=2024-01-01", A2:A100, "<=2024-01-31")

Result: January 2024 total sales

Uses two date criteria on the same date column to define a range.

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.

1

Select sum range

Choose the cells you want to add.

2

Add criteria range

Select the range that contains the first condition.

3

Add criteria

Enter the condition, e.g. "East" or ">1000".

4

Repeat for more criteria

Add additional range/criteria pairs as needed.

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

Practice 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.

Works in Google SheetsCompatible with ExcelIncludes exercises

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

Related Functions

ABS

ABS converts negative numbers to positive, leaving positive numbers unchanged.

ACOS

Returns the angle (in radians) whose cosine is the supplied number.

ASIN

Returns the angle (in radians) whose sine is the supplied number.

ATAN

Returns the angle (in radians) whose tangent is the supplied number.

ATAN2

Returns the angle in radians to a point given by its x and y coordinates.

CEILING.MATH

Rounds up to a multiple of significance, with a mode for negative numbers.

Frequently Asked Questions

What is the difference between SUMIF and SUMIFS?

SUMIF handles one condition. SUMIFS handles one or more conditions.

Can SUMIFS do OR logic?

No, it uses AND logic. For OR logic, add multiple SUMIFS results together.

Can I use wildcards in SUMIFS?

Yes, use * for any characters and ? for a single character.

Can criteria be in different columns?

Yes, each condition can reference a different column as long as ranges are the same size.