How to Use SUMIFS in Google Sheets: Comprehensive Guide

SUMIFS adds up the values in a range that meet several conditions at once. It is the multi-criteria version of SUMIF: total sales for one region in one quarter, expenses in a category above a limit, hours worked by one person in one month.

Syntax

=SUMIFS(sum_range, criteria_range1, criterion1, [criteria_range2, criterion2, ...])

sum_range: The cells to add up. It comes first, unlike SUMIF, where the sum range is last.
criteria_range1: The cells to test against the first criterion.
criterion1: The first condition.
criteria_range2, criterion2, ... [Optional]: More range/condition pairs.

A cell is included only if it meets all the conditions (AND logic). Every range must be the same size.

Basic example

With regions in A2:A100, products in B2:B100 and sales in C2:C100:

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

Returns total sales where the region is North and the product is Widget.

Writing criteria

Criteria work as they do in SUMIF:

Exact: "North" or a cell reference such as E1
Comparison: ">100", "<=50", "<>0"
Wildcards: "Wid*", "?idget"
Blank / not blank: "" and "<>"
Operator plus cell: ">" & E1

Date ranges

The most common use of SUMIFS is totaling between two dates. Use one condition for the start and one for the end on the same range:

=SUMIFS(C2:C100, D2:D100, ">=" & DATE(2026, 1, 1), D2:D100, "<=" & DATE(2026, 3, 31))

With start and end dates in cells F1 and G1:

=SUMIFS(C2:C100, D2:D100, ">=" & F1, D2:D100, "<=" & G1)

Monthly totals from a date column, using the first of each month in F2:

=SUMIFS(C2:C100, D2:D100, ">=" & F2, D2:D100, "<" & EDATE(F2, 1))

OR logic

SUMIFS combines conditions with AND. To get OR, add separate SUMIFS or pass an array of values:

=SUM(SUMIFS(C2:C100, A2:A100, {"North", "South"}))

Totals North plus South. For OR across different columns, use two SUMIFS and subtract the overlap, or use SUMPRODUCT.

Practical patterns

Two-way summary table

Region names down column E, product names across row 1, and one formula for the whole grid:

=SUMIFS($C$2:$C$100, $A$2:$A$100, $E2, $B$2:$B$100, F$1)

Drag it right and down. The $ signs lock the ranges, and mixed references on $E2 and F$1 let the labels shift.

Sum with a minimum threshold

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

The sum range can also be a criteria range.

Exclude a category

=SUMIFS(C2:C100, A2:A100, "North", B2:B100, "<>Refund")

Apply a condition from a dropdown, allowing "all"

=SUMIFS(C2:C100, A2:A100, IF(E1 = "All", "*", E1))

When E1 is All, the wildcard * matches any text.

Common errors

#VALUE!: "Mismatched range sizes". The ranges are not the same dimensions. Check that every range has the same number of rows and columns.
The result is 0 but matches exist. The numbers in sum_range may be stored as text, the criteria column may have trailing spaces, or dates may be text rather than real dates. Clean the data and test the criteria with COUNTIFS to see how many rows match.
Argument order mistakes. Remember sum_range comes first in SUMIFS. Moving from SUMIF is the usual cause of wrong results.
Wrong operator syntax. Comparisons need quotes: ">100", not >100.
Hard-coded dates as text. ">=1/1/2026" may parse differently depending on your locale. Use ">=" & DATE(2026, 1, 1) instead.

SUMIFS vs. SUMIF vs. QUERY vs. SUMPRODUCT

SUMIF is simpler for one condition. SUMIFS is the standard for multi-condition totals. SUMPRODUCT handles unusual logic such as OR across columns. QUERY or a pivot table is easier when you need totals for many combinations at once.

Frequently asked questions

Is SUMIFS case-sensitive? No.

How many conditions can I use? Many. Each is a range/criterion pair.

Can I use SUMIFS with a single condition? Yes, but remember the different argument order from SUMIF.

Can I use whole columns? Yes, SUMIFS(C:C, A:A, "North") works and adapts as data grows, with a small cost on huge sheets.

Related Functions

SUMIF: Sum with a single condition.
COUNTIFS: Count with several conditions.
AVERAGEIF: Average with a condition.
SUM: Add up a series of numbers.
QUERY: Group and total data with SQL-like syntax.

SUMIFS