What is SUMIF in Google Sheets?
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.
When to use it
Use SUMIF when you need one rule: revenue by region, hours by project code, or expenses where category equals Travel.
When to skip it
Skip SUMIF when you need two or more AND conditions on different columns. Use SUMIFS instead. For counting matches without summing, use COUNTIF.
How it works
-
1
Pick sum_range: the numeric column to add, such as Revenue in column C.
-
2
Pick criteria_range: the column to test, often the same height as sum_range.
-
3
Write criterion as a quoted string, cell reference, or expression like ">100".
-
4
SUMIF scans criteria_range row by row and adds the parallel sum_range cell when the test passes.
-
5
Use wildcards * and ? inside quotes for partial text matches on labels.
-
6
Pair with absolute references ($C$2:$C) when copying the formula across summary columns.
Examples in Google Sheets
Sum sales for one rep
=SUMIF(A2:A500, "Jordan", C2:C500) adds revenue in column C where owner in column A is Jordan.
Sum above a threshold
=SUMIF(D2:D, ">=1000", E2:E) totals amounts in E where score in D is at least 1000.
Wildcard department match
=SUMIF(B2:B, "Marketing*", C2:C) includes Marketing, Marketing Ops, and similar labels.
Better Sheets resources
Common mistakes
-
Mismatched heights between sum_range and criteria_range, which returns wrong totals or errors.
-
Putting the criterion in the wrong argument order compared with SUMIFS.
-
Comparing dates stored as text, so SUMIF finds zero matches silently.
-
Using SUMIF on whole columns A:A and C:C in every summary cell on a huge sheet, slowing recalc.
-
Expecting OR logic across two columns from one SUMIF. Use two SUMIFs added together or SUMIFS.
Frequently asked questions
- SUMIF vs SUM?
- SUM adds every number in a range. SUMIF adds only rows where a separate column matches your rule.
- Can criterion be a cell reference?
- Yes. Put the test value in a settings cell and reference it instead of a quoted string.
- SUMIF with dates?
- Compare real date values or use DATE inside quotes. Text dates often fail to match.
- SUMIF vs SUMIFS?
- SUMIF handles one condition. SUMIFS accepts multiple range-criterion pairs with AND logic.
- Wildcards in SUMIF?
- Use * for any text run and ? for one character inside the quoted criterion.
- SUMIF on text numbers?
- SUMIF still adds if sum_range cells are numeric. Clean imports with VALUE if needed.
- Why is my SUMIF zero?
- Check extra spaces in criteria_range, wrong quote type, or criterion that does not match any row.
- SUMIF across sheets?
- Reference another tab in both ranges, such as Sales!A2:A and Sales!C2:C.
Related Tutorials
Watch how SUMIF works
How to Use AND(), OR() in IF() in Google Sheets For Multiple Criteria
Learn Google Sheet Formulas The Hard Way
Ultimate Checklist in Google Sheets
Related blog posts
Guides that explain SUMIF 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 terms
SUMIFS
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.
Read guide →COUNTIF
COUNTIF returns how many cells in a range meet a single criterion. It powers KPI tiles like open ticket count, rows flagged Yes, or cells above a quota. The criterion follows the same string, comparison, and wildcard rules as SUMIF.
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 →Google Sheets formula syntax
Every formula starts with = followed by a function name and arguments in parentheses. Ranges use A1 notation, text sits in quotes, and operators like + and & combine values. Sheets recalculates when inputs change, so syntax errors show as #NAME? or #ERROR! in the cell.
Read guide →Done reading about SUMIF?
Membership unlocks 600+ 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.