Google Sheets provides powerful functions for adding up data efficiently. Whether you need to sum all numbers, filter sums based on a condition, or apply multiple criteria, SUM, SUMIF, and SUMIFS are essential tools for data analysis.
In this guide, we’ll cover these functions with syntax, examples, and real-world applications.
1. SUM Function (Add All Numbers)
What Does SUM Do?
The SUM function adds up all numeric values in a given range.
Syntax:
=SUM(range)
Example:
To sum all values in A1 to A10:
=SUM(A1:A10)
✅ Result: Returns the total sum of numbers in the range.
Use Case: Ideal for totaling sales, expenses, or any numerical dataset.
2. SUMIF Function (Conditional Summing)
What Does SUMIF Do?
The SUMIF function adds numbers that meet a specific condition.
Syntax:
=SUMIF(range, criteria, [sum_range])
Example 1: Summing Sales Over $1000
To sum sales in A1:A10 where values are greater than 1000:
=SUMIF(A1:A10, ">1000")
✅ Result: Adds only numbers above 1000.
Example 2: Summing Sales by Category
To sum sales in B1:B10 where the category in A1:A10 is “Electronics”:
=SUMIF(A1:A10, "Electronics", B1:B10)
✅ Result: Adds up sales only for “Electronics.”
Use Case: Great for analyzing sales by category, expenses by department, or filtering specific transactions.
3. SUMIFS Function (Summing Based on Multiple Conditions)
What Does SUMIFS Do?
The SUMIFS function adds numbers that meet multiple conditions.
Syntax:
=SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2, ...])
Example 1: Summing Sales Over $1000 in a Specific Region
To sum sales (column B) above $1000 in the “East” region (column A):
=SUMIFS(B2:B100, A2:A100, "East", B2:B100, ">1000")
✅ Result: Adds sales over $1000 for the “East” region.
Example 2: Summing Employee Salaries Based on Department and Experience
To sum salaries (column C) for employees in the “IT” department (column A) with more than 5 years of experience (column B):
=SUMIFS(C2:C100, A2:A100, "IT", B2:B100, ">5")
✅ Result: Adds up salaries for IT employees with 5+ years of experience.
Use Case: Perfect for advanced financial analysis, sales reports, and HR analytics.
Common Issues & Solutions
🚨 Problem: SUM function isn’t working correctly. ✅ Solution: Ensure the range contains only numbers, as SUM ignores text.
🚨 Problem: SUMIF doesn’t recognize criteria. ✅ Solution: Ensure text criteria are enclosed in quotes (e.g., “Electronics”).
🚨 Problem: SUMIFS isn’t returning expected results. ✅ Solution: Check that all criteria ranges match the sum range size.
Final Thoughts: When to Use Each Function
| Function | Purpose |
|---|---|
SUM | Adds all numbers in a range |
SUMIF | Adds numbers matching one condition |
SUMIFS | Adds numbers meeting multiple conditions |
🚀 Next Steps:
Explore AVERAGEIF, and MAXIFS for more data analysis techniques!
📢 Have questions? Drop them in the comments!