SUM Function in Google Sheets
Adds up numbers in a range or list of values. The first function nearly every spreadsheet user learns and still the most-used function on the planet.
Syntax
SUM(value1, [value2, ...])Returns: The total of all numeric values in the arguments. Non-numeric values (text, blanks) are silently ignored.
Excel equivalent: SUM (identical)
Parameters
| Name | Required | Description |
|---|---|---|
| value1, value2, ... | Required | Numbers, cell references, ranges, or expressions to add. Up to 30 arguments; each argument can be a range with thousands of values. |
Examples
Sum a column
=SUM(A2:A100)Adds all numbers in A2 through A100. Text values are ignored, not counted as zero.
Sum multiple disjoint ranges
=SUM(A2:A10, C2:C10, E2)You can pass any combination of ranges and single cells — SUM adds everything together.
Sum across sheets
=SUM(Q1!B2:B10, Q2!B2:B10, Q3!B2:B10, Q4!B2:B10)Each sheet contributes its range; SUM returns the grand total across all four quarters.
When to use an alternative
- SUMIF / SUMIFS — You want to sum only values that match a condition.
- SUMPRODUCT — You need to multiply pairs of values before summing (weighted totals).
- QUERY — You want SQL-style SUM with GROUP BY for category totals.
Common errors and how to fix them
#VALUE!
Cause: Text in a referenced cell that Sheets can't ignore (e.g., explicit text-typed cells with numbers stored as text).
Fix: Use VALUE() to convert text-typed numbers, or filter the range first.
Returns 0 unexpectedly
Cause: Numbers stored as text — they look like numbers but are left-aligned and ignored by SUM.
Fix: Wrap the range with ARRAYFORMULA(VALUE(range)) or fix the underlying cells to numeric type.
Related functions
Frequently Asked Questions
Does SUM ignore text and empty cells?
Yes. SUM silently skips text values, blank cells, and boolean cells (TRUE/FALSE) when summing a range. It only adds cells that contain numbers. This is usually what you want, but be aware that 100 looking numbers + 1 text-typed number will sum to just the 100 numerics.
What's the maximum range SUM can handle?
Practically unlimited — SUM works fine on full-column references like A:A even on the 10-million-cell Sheets limit. Performance only degrades when paired with volatile functions inside the range.