Need to add up numbers in Google Sheets—but only when they meet specific conditions? That’s where SUMIF and SUMIFS come in. These powerful functions let you sum data based on one or more criteria, making them perfect for sales reports, budgeting, inventory tracking, and more. Whether you’re a beginner or just trying to tidy up your spreadsheets, understanding these formulas can save you tons of time.
In this article, we’ll break down the difference between SUMIF and SUMIFS, show you exactly how to use them, and provide real-life examples to make things click.
What is SUMIF?
SUMIF is used to add values in a range that meet a single condition.
=SUMIF(range, criterion, [sum_range])- range: The range to evaluate against the criterion
- criterion: The condition to check (e.g., “Apples”, “>100”)
- sum_range: (Optional) The actual range of cells to sum
What is SUMIFS?
SUMIFS is used when you want to apply multiple conditions to your sum. It’s like an upgraded version of SUMIF for more complex filtering.
=SUMIFS(sum_range, criteria_range1, criterion1, [criteria_range2, criterion2], ...)- sum_range: The range to add up
- criteria_range: The range to check against each criterion
- criterion: The value or condition that must be met
Real-Life Example: Sales by Product and Region
Imagine you manage regional sales data and want to calculate total sales for a specific product or region. Here’s a simplified dataset:
| Product | Region | Sales |
|---|---|---|
| Apples | North | 150 |
| Apples | South | 200 |
| Oranges | North | 180 |
| Apples | North | 170 |
Use SUMIF: Total Sales for Apples
=SUMIF(A2:A5, "Apples", C2:C5)Result: 150 + 200 + 170 = 520
Use SUMIFS: Total Sales for Apples in the North
=SUMIFS(C2:C5, A2:A5, "Apples", B2:B5, "North")Result: 150 + 170 = 320
Common Use Cases for SUMIF and SUMIFS
- Track total sales by product or salesperson
- Sum expenses in a budget based on category
- Calculate attendance or hours worked by employee
- Analyze monthly or quarterly performance data
Tips for Using SUMIF and SUMIFS
- Use cell references instead of hardcoding criteria (e.g.,
=SUMIF(A2:A10, E1, C2:C10)) - Wildcards like
*and?work for partial matches - Text criteria should be in quotes (e.g., “Apples”)
- Numeric conditions can use
>,<, etc. (e.g., “>100”)
Quick-Reference Cheat Sheet
| Task | Formula |
|---|---|
| Sum all sales for “Apples” | =SUMIF(A2:A, “Apples”, C2:C) |
| Sum if sales are above 100 | =SUMIF(C2:C, “>100”) |
| Sum “Apples” sold in “North” region | =SUMIFS(C2:C, A2:A, “Apples”, B2:B, “North”) |
| Sum sales using criteria in cells | =SUMIFS(C2:C, A2:A, E1, B2:B, F1) |
Whether you’re summing sales, expenses, or anything in between, SUMIF and SUMIFS are essential tools in Google Sheets. They help you automate calculations and analyze data efficiently without manual filtering. Once you get comfortable with these formulas, you’ll start thinking of ways to make your spreadsheets smarter and more insightful.
Start small, experiment with your own data, and soon you’ll be summing like a pro!