SUM

Add up numbers in a range with Excel's SUM function.

Math & Trigonometry
|
Excel All versions
|
Google Sheets Supported

Spreadsheet editor

Syntax

=SUM(number1, number2, ...) Returns: Number

Arguments

Argument Required Description
number1 Yes The first number or range of cells to be added.
number2 No Subsequent numbers or ranges to be added.
... No You can include multiple numbers or ranges separated by commas to add them together.

About

SUM adds numbers together from ranges, individual cells, or a mix of both. Use it whenever you need to total values, whether it's monthly expenses, quarterly sales, or budget items. Instead of writing out each cell with plus signs (like A1+A2+A3), SUM lets you reference entire ranges (like A1:A3) that adjust automatically when you add or remove rows.

What makes SUM reliable is how it handles messy data. Text values get ignored instead of causing errors, so if someone types "pending" in a cell, your total still calculates. When you insert or delete rows within a range, SUM updates automatically, unlike manual addition formulas that break with #REF! errors. This makes it ideal for financial reports, budgets, and any data that changes over time.

For basic addition, use SUM. When you need to add only values that meet specific criteria, try SUMIF for single conditions or SUMIFS for multiple conditions. Need to ignore filtered rows? Check out SUBTOTAL.

Examples

Combining multiple ranges

Use SUM to add values from separate ranges in one formula. This product sales tracker combines Q1, Q2, and Q3 from all three products. Edit any sales figure and watch the annual total update.

Spreadsheet editor

Handling text and empty cells

See how SUM ignores text values and empty cells. This payment tracker has 'TBD' mixed with numbers. Change 'TBD' to 800 and watch the total jump from 3550 to 4350.

Spreadsheet editor

Dynamic ranges that adjust automatically

Try inserting a new row between any two days in this expense tracker. The SUM formula automatically expands to include your new entry. This is why ranges beat individual cell references.

Spreadsheet editor

Watch out for

⚠

Using individual cell references instead of ranges

Writing =SUM(A1, A2, A3, A4) won't expand when you insert rows. Add a row between A2 and A3, and your formula misses it.

→ Use ranges like =SUM(A1:A4). Ranges automatically include any rows inserted within them, keeping totals accurate as data grows.

⚠

SUM includes hidden and filtered rows

When you filter data to show only certain rows, SUM still includes the hidden rows in its calculation. The displayed total doesn't match what you see onscreen.

→ Use SUBTOTAL with function number 9 instead: =SUBTOTAL(9, A1:A10). This calculates based only on visible rows after filtering.

⚠

Error values break the entire formula

If any cell in the range contains an error value like #N/A or #DIV/0!, SUM returns that error instead of a total. One bad cell breaks the whole calculation.

→ Clean up errors first, or use AGGREGATE with function number 9, which ignores errors: =AGGREGATE(9, 6, A1:A10).

⚠

Text values get silently ignored

SUM skips text values without warning. If cells contain "TBD" or "pending" mixed with numbers, those cells don't count toward your total, which might hide incomplete data.

→ Clean your data before summing, or use COUNT alongside SUM to verify how many numeric values exist. If you need to handle specific text scenarios, explore SUMIF.

Tips & notes

SUM accepts up to 255 arguments, so you can combine multiple ranges and individual values: =SUM(A1:A10, C1:C5, 500). Empty cells are treated as zero, but text and logical values are ignored. For conditional totals based on criteria, use SUMIF or SUMIFS. If you need to multiply ranges while summing, check out SUMPRODUCT.

Common questions

Can SUM handle negative numbers?

Yes, SUM adds all numbers with their signs. If you mix positive values (income) and negative values (expenses), you get the net total. For example, =SUM(100, -30, 50, -20) returns 100.

What happens with error values in the range?

SUM returns an error if any cell contains #N/A, #VALUE!, #DIV/0!, or other error values. The formula fails rather than ignoring the error. Use AGGREGATE to sum while skipping errors.

Does SUM work with time values?

Yes, but times are stored as decimal fractions of a day. Summing 8:00 AM (0.333) and 4:00 PM (0.667) gives 1.0 (24 hours). Format the result cell as time, or multiply by 24 to display hours as a number.

Can I sum entire columns like =SUM(A:A)?

Yes, =SUM(A:A) adds all numeric values in column A. Useful for growing datasets, but it can slow down large files since Excel checks over a million rows. Use specific ranges when possible for better performance.