Region and quarter revenue
=SUMIFS(C2:C, A2:A, "West", B2:B, "Q1") totals column C where region is West and quarter is Q1.
What is SUMIFS in Google Sheets?
SUMIFS adds values in sum_range only when every criteria_range passes its paired test. Argument order is sum_range first, then alternating criteria_range and criterion pairs. All conditions use AND logic: every test must be TRUE on the same row.
When to use it
Use SUMIFS for filtered totals: West region and Q1 month, or product line and active status, without helper columns.
When to skip it
Skip SUMIFS for a single simple rule. SUMIF is shorter. For OR logic across one column, add multiple SUMIFs or use QUERY.
How it works
-
1
Start with sum_range, the numeric column to total.
-
2
Add criteria_range1 and criterion1, then more pairs for each extra rule.
-
3
Every criteria_range must match the same row count as sum_range.
-
4
Each criterion can be text, number, comparison, cell reference, or wildcard string.
-
5
SUMIFS skips rows where any single test fails.
-
6
Lock ranges with $ when building a summary table you copy across headers.
Examples in Google Sheets
Active deals above size
=SUMIFS(Amount!D:D, Status!E:E, "Open", Amount!D:D, ">5000") sums open deals over 5000.
Rep and product line
=SUMIFS(Revenue, Owner, H1, SKU, I1) uses dropdown cells for rep and SKU filters on a dashboard.
Build this without starting from a blank cell
Use a Better Sheets tool for SUMIFS, then watch a walkthrough when you want the full pattern.
Better Sheets resources
Common mistakes
- Reversing SUMIF argument order. SUMIFS always leads with sum_range.
- Criteria ranges shorter than sum_range, causing partial matches or errors.
- Expecting OR between columns from one SUMIFS call. Every pair is AND.
- Using different row offsets on each criteria_range so tests no longer align.
- Hard-coding month names in twelve formulas instead of a single criterion cell reference.
Frequently asked questions
- SUMIFS vs SUMIF?
- SUMIF handles one condition. SUMIFS stacks multiple range-criterion pairs with AND logic.
- How many criteria can SUMIFS use?
- Google Sheets allows many pairs. Practical limits are readability and range alignment.
- SUMIFS with OR logic?
- Not in one call. Add separate SUMIFS results or use QUERY with OR in the where clause.
- SUMIFS vs FILTER plus SUM?
- Both work. SUMIFS is compact for AND rules. FILTER plus SUM is flexible for complex arrays.
- SUMIFS with dates?
- Use DATE literals or date cells in criteria. Keep date columns true date values.
- SUMIFS blank criterion?
- Test blanks with "" on the matching criteria_range for that column.
- Why SUMIFS returns zero?
- One criterion fails on every row, often from spaces, wrong case, or misaligned ranges.
- SUMIFS across sheets?
- Each range can include a tab name. All ranges must still align by row.
Related Tutorials
Watch how SUMIFS works
How to Use AND(), OR() in IF() in Google Sheets For Multiple Criteria
Learn Google Sheet Formulas The Hard Way
3 More Ways to Count in Google Sheets
Search Every Function in Google Sheets
Related blog posts
Guides that explain SUMIFS in more depth.
The Most Popular Google Sheet Tutorial on the IF() Formula
The IF() formula is a logical function that makes it easy for users to compare a certain value and an expected value. It takes a look at a value to see if basic conditions are met.
Read post →Learn Google Sheet Formulas The Hard Way
It is so stupidly simple as well that you might not do it and you might not think you'll get enough out of it. But I can tell you unequivocally: This is very much how I learned a lot of Google Sheet formulas.
Read post →The Best Google Sheet Tutorial for Beginners on the IF() Formula
Dive into Google Sheets with our free beginner tutorials on the powerful =IF() formula. Practical applications, templates, and tools included!
Read post →Related glossary terms
SUMIF
SUMIF totals values in a sum_range when a matching row in criteria_range meets your criterion. It is the classic single-condition sum: add column B where column A equals West. Wildcards and comparison operators work in the criterion string.
Read guide →COUNTIFS
COUNTIFS counts how many rows satisfy every criterion at once. You pass criteria_range1, criterion1, criteria_range2, criterion2, and so on. Unlike COUNTIF, there is no separate count range: each pair tests the same row index across the sheet.
Read guide →QUERY
QUERY runs a SQL-like statement against a range of cells and returns a new table of results. You pass a data range, a query string with SELECT, WHERE, ORDER BY, and optional headers. It is the fastest way to filter, sort, group, and pivot sheet data without helper columns.
Read guide →FILTER
FILTER returns only the rows from a range that meet one or more conditions. Conditions are boolean arrays the same height as the data. FILTER spills results automatically, so it replaced many INDEX/SMALL helper-column patterns for live subsets.
Read guide →Done reading about SUMIFS?
Membership unlocks 636+ tutorials, unlimited generators, and every template. Practical lessons. Zero fluff.
Need this once
Jump to a free tool or a single tutorial for this topic.
Learning Sheets for real
Unlock the full library, generators, and templates with membership.