Google Sheets SUMIFS Function: Syntax, Examples & Tips

What Is SUMIFS?

SUMIFS is the multi-condition version of SUMIF. It sums values in a range where all specified conditions are met simultaneously. If you need to filter by product and region and date range, SUMIFS is your function.

How SUMIFS Works

SUMIFS checks each row against every criterion you provide. A value in sum_range is included in the total only when all corresponding criteria are satisfied. It uses AND logic — every condition must be true.

Key Difference from SUMIF

In SUMIFS, the sum_range comes first, followed by the criteria pairs. This is the opposite order from SUMIF:

SUMIF:  =SUMIF(criteria_range, criterion, sum_range)
SUMIFS: =SUMIFS(sum_range, criteria_range1, criterion1, ...)

Basic Examples

Sum with two conditions

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

Sums values in column C where column A is "Widget" and column B is "East".

Sum within a date range

=SUMIFS(D2:D100, E2:E100, ">="&DATE(2025,1,1), E2:E100, "<="&DATE(2025,12,31))

Sums values in column D where column E falls within the year 2025.

Sum with a numeric threshold and text condition

=SUMIFS(C2:C100, A2:A100, "Premium", C2:C100, ">1000")

Sums values in column C where column A is "Premium" and the value itself exceeds 1000.

Using Cell References as Criteria

=SUMIFS(D2:D100, A2:A100, G1, B2:B100, G2)

Sums column D where column A matches cell G1 and column B matches cell G2. This makes your formula dynamic — change G1 or G2 to update the result instantly.

Using Comparison Operators

All standard operators work the same as in SUMIF:

Using Wildcards

SUMIFS supports the same wildcards as SUMIF:

• * — matches any sequence of characters
• ? — matches any single character

=SUMIFS(C2:C100, A2:A100, "Pro*", B2:B100, "Active")

Sums where column A starts with "Pro" and column B is "Active".

Advanced Examples

Sum sales by rep in a specific month

=SUMIFS(D2:D500, B2:B500, "Sarah", C2:C500, ">="&DATE(2025,3,1), C2:C500, "<"&DATE(2025,4,1))

Sum excluding a specific category

=SUMIFS(C2:C100, A2:A100, "<>Returns")

Sums all values in C where A is anything except "Returns".

Sum with three conditions

=SUMIFS(E2:E100, A2:A100, "Laptop", B2:B100, "Online", D2:D100, ">=4")

Sums column E where product is "Laptop", channel is "Online", and rating is 4 or higher.

Common Use Cases

• Sales dashboards: Sum revenue by product + region + time period
• Budget analysis: Total expenses by department + quarter
• HR: Sum hours worked by employee + project + month
• Marketing: Sum conversions by campaign + channel
• Inventory: Sum stock by warehouse + product category

Tips and Best Practices

• All criteria_ranges must be the same size as sum_range — same number of rows.
• SUMIFS uses AND logic — all conditions must be met. For OR logic, use multiple SUMIFS added together or use SUMPRODUCT.
• Text criteria are not case-sensitive.
• For date criteria, always use the DATE() function or a cell reference — avoid typing dates as text.
• Use named ranges for readability in complex formulas.
• When building dashboards, reference criteria cells instead of hardcoding values.

OR Logic with SUMIFS

SUMIFS only supports AND logic natively. To get OR logic, add multiple SUMIFS:

=SUMIFS(C:C, A:A, "Apples") + SUMIFS(C:C, A:A, "Oranges")

Or use SUMPRODUCT for more flexibility:

=SUMPRODUCT((A2:A100="Apples")+(A2:A100="Oranges"), C2:C100)

Common Errors

• Mismatched range sizes: All ranges must have the same number of rows. =SUMIFS(C2:C100, A2:A50, "X") will error.
• #VALUE! error: Often caused by using a range from a different sheet with incorrect syntax.
• Unexpected zero: Usually means no rows match all conditions — double-check your criteria.
• Forgetting the argument order: Remember sum_range comes first in SUMIFS, unlike SUMIF.